Ad alcune domande sono necessari due passaggi per rispondere. “Quali ordini sono sopra la media?” richiede che tu trovi prima la media e poi confronti ogni ordine con essa. Le sottoquery SQL ti consentono di scrivere entrambi i passaggi come un’unica istruzione.

Questo tutorial copre ogni tipo utilizzando due piccole tabelle. Abbiamo eseguito queste query in SQLite 3.50 e i risultati mostrati provengono da tale esecuzione. La sintassi standard delle subquery è ampiamente supportata e segnaliamo i punti in cui i database differiscono.

Cos’è una sottoquery?

Una sottoquery, chiamata anche query interna o annidata, è un’istruzione SELECT inserita all’interno di un’altra istruzione, tra parentesi tonde. Concettualmente, la query interna produce un risultato che viene poi utilizzato dalla query esterna.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Le tabelle campione

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Tieni presente che Dev non ha ordini. Questo dettaglio è importante per diversi esempi di seguito.

Panoramica dei tipi di sottoquery

Sottoquery scalari

Una sottoquery scalare restituisce esattamente un valore, quindi puoi utilizzarla ovunque abbia senso un singolo valore. Ecco la query di prima, con il suo risultato. La media di tutti gli ordini è 980:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

Non è possibile scrivere WHERE total > AVG(total) perché gli aggregati non sono consentiti direttamente in WHERE. La sottoquery aggira questo problema calcolando il valore separatamente.

Cosa succede se una sottoquery “scalare” restituisce più righe? La maggior parte dei database, inclusi PostgreSQL, MySQL e SQL Server, generano un errore. SQLite no. Nel nostro test ha utilizzato silenziosamente solo la prima riga, che potrebbe nascondere un vero bug, quindi non dipendere da questo comportamento.

DENTRO e NON DENTRO

Quando la query interna restituisce un elenco, utilizzare IN. Questo trova i clienti con almeno un ordine superiore a 1000:

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

-- Asha
-- Chitra

NOT IN fa il contrario, trovando clienti che non hanno mai ordinato:

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

-- Dev

Qui funziona, ma NOT IN presenta un caso limite pericoloso, che tratteremo di seguito.

Sottoquery in FROM

Una sottoquery in FROM agisce come una tabella temporanea, spesso chiamata tabella derivata. È utile quando è necessario eseguire l’aggregazione due volte, come in questa query per la spesa media per cliente in ciascuna città:

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

La query interna totalizza ciascun cliente e quella esterna calcola la media di tali totali. Assegnare un alias alla tabella derivata è una buona abitudine, poiché alcuni database lo richiedono. Se GROUP BY è nuovo per te, consulta SQL GROUP BY e HAVING.

Sottoquery in SELECT

Una sottoquery scalare può anche aggiungere una colonna calcolata. Questo conta gli ordini per cliente:

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

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev appare con zero. Un inner join avrebbe eliminato completamente Dev, perché Dev non ha righe corrispondenti. Potresti ottenere lo stesso risultato con LEFT JOIN e GROUP BY, come spiegato in Spiegazione dei join SQL.

Sottoquery correlate

Anche l’esempio precedente è correlato, poiché la query interna fa riferimento a c.id dalla query esterna. Una sottoquery non correlata può essere eseguita da sola. Uno correlato non può, perché dipende da un valore della riga esterna corrente.

Ciò trova ordini superiori alla media dello stesso cliente:

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

Ecco come leggerlo. Per l’ordine 101, la query interna calcola la media degli ordini del cliente 1 (1200 e 800), che restituisce 1000. Poiché 1200 è maggiore, la riga viene mantenuta. Per l’ordine 102 si applica la stessa media, ma 800 non è maggiore, quindi viene eliminato. Il singolo ordine del cliente 2 è uguale alla sua media, quindi viene eliminato anch’esso.

“Una volta per riga” descrive la logica e gli ottimizzatori di query spesso la riscrivono in qualcosa di molto più efficiente. Tuttavia, vale la pena esaminare una sottoquery correlata lenta. Un indice sulla colonna interna, qui orders.customer_id, può fare una grande differenza. Vedi Spiegazione degli indici SQL.

ESISTE e NON ESISTE

EXISTS pone una domanda sì o no: la query interna restituisce almeno una riga? Può fermarsi alla prima partita e ciò che selezioni all’interno non ha importanza, motivo per cui le persone spesso scrivono SELECT 1.

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

-- Asha, Ben, Chitra

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

-- Dev

La trappola NOT IN NULL

Questo è uno dei bug delle subquery più comuni. Abbiamo creato una tabella con un ID reale e un NULL, quindi abbiamo eseguito entrambi i moduli su di essa:

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

Perché il primo non restituisce nulla? id NOT IN (1, NULL) significa id <> 1 AND id <> NULL. Qualsiasi confronto con NULL è sconosciuto anziché vero, quindi l’intera condizione non potrà mai essere vera. Non sono presenti messaggi di errore né avvisi, solo un risultato vuoto che sembra plausibile.

Sottoquery vs JOIN vs CTE

Molte query possono essere scritte in più di un modo. In precedenza abbiamo trovato clienti con un ordine di grandi dimensioni utilizzando IN. Ecco lo stesso risultato con un join:

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Si noti il DISTINCT. Un’unione può produrre duplicati se un cliente ha diverse corrispondenze, mentre IN e EXISTS no.

Le prestazioni variano in base al database e ai dati e gli ottimizzatori moderni spesso producono piani simili. Quindi scegli prima la forma più chiara, poi controlla EXPLAIN se risulta essere lenta.

Errori comuni

• Utilizzando = con un risultato su più righe. Utilizza IN o assicurati che la query interna restituisca una riga.
• Utilizzando NOT IN su una colonna nullable. Puoi perdere silenziosamente ogni riga.
• Utilizzo di alias poco chiari. Nelle query correlate, qualifica ogni colonna, altrimenti potrebbe essere associata alla tabella sbagliata.
• Nidificazione profonda. Tre livelli sono difficili da leggere, quindi convertili in CTE.
• Aggiungere ORDER BY all’interno. Di solito non ha alcun effetto sul risultato esterno e alcuni database lo rifiutano.

Considerazioni finali

Le sottoquery rappresentano un modo naturale per esprimere domande in due passaggi. Inizia con le sottoquery scalari e IN, quindi esercitati con quelle correlate ragionando su una singola riga esterna alla volta. E ricorda NOT EXISTS ogni volta che sono possibili NULL. Per esercitarti, scrivi una domanda in tre modi, con una sottoquery, un join e un CTE, e confronta sia i risultati che la leggibilità.

Domande frequenti

Continua a imparare