Subquery in SQLite: SELECT annidate in WHERE, FROM e SELECT

Una subquery è una SELECT dentro una SELECT

Una subquery è esattamente quello che sembra: un'istruzione SELECT infilata dentro un'altra istruzione, racchiusa tra parentesi. SQLite esegue la query interna, prende il risultato e lo passa a quella esterna.

Prepariamo un piccolo esempio da riutilizzare:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders;

Cinque ordini, quattro clienti, due dei quali non hanno ordinato niente. Lo useremo per tutta la pagina.

Subquery nella WHERE: filtrare in base a una lista

La forma più comune: ricavi una lista di id con una query interna, poi filtri la query esterna in base a quella lista.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

La query interna produce ogni customer_id che compare in orders. La query esterna tiene solo i clienti il cui id è in quella lista. Compaiono Cleo, Boris e Ada; Dmitri (nessun ordine) no.

IN (SELECT ...) è lo schema di base per "righe di A che hanno una corrispondenza in B". Leggilo mentalmente come "dove il valore di questa colonna è uno dei valori restituiti dalla query interna".

NOT IN: attenzione ai NULL

La domanda opposta, "quali clienti non hanno ordinato?", è a una riga di distanza:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Qui funziona. Ma NOT IN ha uno spigolo tagliente: se la subquery restituisce anche un solo NULL, l'intero NOT IN diventa NULL (che non è TRUE) e ottieni zero righe. Sorprendente e silenzioso.

L'abitudine sicura quando usi NOT IN su una colonna che potrebbe contenere NULL:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Oppure usa NOT EXISTS, che questo problema non ce l'ha proprio. Ci arriviamo tra poco.

Subquery scalari: una riga, una colonna

Una subquery scalare restituisce un singolo valore (una riga, una colonna) e puoi usarla ovunque sia atteso un valore.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name, total
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE total = (SELECT MAX(total) FROM orders);

La SELECT MAX(total) FROM orders interna restituisce 200. La query esterna filtra poi gli ordini che corrispondono a quel valore. Utile ogni volta che devi confrontare con un'aggregazione.

Puoi usare una subquery scalare anche nell'elenco della SELECT per aggiungere un valore calcolato a ogni riga:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
FROM customers;

Per ogni riga di customers la query interna viene eseguita una volta, con customers.id inserito al suo posto. È una subquery correlata: ne parliamo più sotto. Per i casi "un numero per riga" come questo, una LEFT JOIN con GROUP BY di solito è più veloce, ma la forma scalare si legge benissimo.

EXISTS: controllare solo se c'è una corrispondenza

EXISTS è il cugino più discreto di IN. Non gli interessano i valori: controlla solo se la subquery restituisce almeno una riga. Di solito dentro si scrive SELECT 1, perché la colonna non conta.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.total > 100
);

Questa query trova i clienti che hanno fatto almeno un ordine sopra 100. La query interna fa riferimento a c.id della query esterna: è questo che la rende correlata. SQLite smette di scorrere la tabella interna appena trova una corrispondenza, ed è per questo che EXISTS spesso batte IN per le domande del tipo "questa riga ha una riga collegata?".

La negazione, NOT EXISTS, è il modo sicuro rispetto ai NULL per chiedere "nessuna riga collegata":

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Subquery nel FROM: una tabella derivata

Una subquery può stare ovunque possa stare una tabella, compresa la clausola FROM. La query interna diventa una "tabella derivata" temporanea e con un nome, su cui puoi fare join, filtri o aggregazioni.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT country, AVG(customer_total) AS avg_per_customer
FROM (
    SELECT c.country, c.id, SUM(o.total) AS customer_total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
) AS per_customer
GROUP BY country;

La query interna calcola un totale per cliente. Quella esterna fa la media di quei totali per paese. Le aggregazioni in due fasi come questa sono proprio il motivo per cui esistono le tabelle derivate: quando non riesci a fare tutto con un solo GROUP BY.

L'alias AS per_customer è obbligatorio: ogni tabella derivata deve avere un nome.

Subquery correlate: eseguite per ogni riga esterna

Una subquery è correlata quando fa riferimento a una colonna della query esterna. SQLite deve rivalutare la query interna per ogni riga esterna: è flessibile, ma può diventare costoso.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name,
       (SELECT MAX(total)
        FROM orders
        WHERE customer_id = customers.id) AS biggest_order
FROM customers;

Per ogni cliente, trova il suo ordine più grande. La query interna dipende da customers.id, quindi viene eseguita una volta per cliente. I clienti senza ordini ottengono NULL, che è proprio quello che vorresti.

Le subquery correlate sono la scelta naturale per "per ogni riga di A, calcola qualcosa da B". Se la tabella è piccola o la ricerca usa un indice, vanno bene. Su tabelle grandi senza indici di supporto, misura le prestazioni prima di andare in produzione: una JOIN con GROUP BY spesso è più veloce.

Subquery o JOIN: quale scegliere?

Queste due query rispondono alla stessa domanda:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Subquery
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

-- JOIN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

Entrambe restituiscono le stesse righe. L'ottimizzatore di SQLite riscrive spesso una forma nell'altra internamente. Scegli in base alla leggibilità:

• Usa una subquery quando devi solo filtrare e non vuoi che le colonne della tabella interna finiscano nel risultato.
• Usa una JOIN quando il risultato ha bisogno di colonne di entrambe le tabelle.
• Usa EXISTS quando chiedi "esiste almeno una riga collegata?": è più chiaro ed evita le trappole dei NULL di IN/NOT IN.

Nel dubbio, scrivi la versione che si spiega da sola quando la leggi ad alta voce.

Un errore comune: subquery che restituiscono più righe

Una subquery usata con = deve restituire al massimo una riga. Se ne restituisce di più, SQLite ne sceglie una (di fatto a caso) e ottieni risultati sbagliati in silenzio, senza nessun errore.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Rischioso: e se ci fosse più di un cliente del Regno Unito?
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'UK');

Usa IN quando la query interna potrebbe restituire più righe:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'UK');

Se ti aspetti esattamente una riga e vuoi garantirlo, aggiungi LIMIT 1 e un ORDER BY, così almeno la scelta è deterministica. Meglio ancora: scrivi la query in modo che una sola riga sia garantita dai dati (filtra su una colonna univoca).

Prossimo passo: Common Table Expressions

Le subquery nel FROM diventano scomode in fretta, soprattutto quando ti serve la stessa tabella derivata due volte o quando l'annidamento arriva a tre livelli. Le Common Table Expressions (WITH ... AS (...)) ti permettono di dare un nome a una subquery all'inizio e di richiamarla per nome nel resto dell'istruzione. È la prossima pagina.