Subquery correlate ed esistenza in SQL

Nella lezione precedente abbiamo visto come utilizzare le subquery per usare il risultato di una query all'interno di un'altra. 

Esiste però un tipo di subquery che funziona in modo leggermente diverso: la subquery correlata. In questo caso la query interna fa riferimento a una o più colonne della query esterna.

Questo meccanismo è particolarmente utile quando vogliamo verificare se, per ogni riga della query esterna, esiste una determinata corrispondenza. Per farlo possiamo utilizzare gli operatori EXISTS e NOT EXISTS.

Cosa sono le subquery correlate

Una subquery correlata è una subquery che fa riferimento a una colonna della query esterna.

Per esempio, in un database di un negozio, abbiamo le tabelle clienti e prodotti, dove nella tabella ordini è presente la colonna cliente_id che indica quale utente ha effettuato l'acquisto (l'abbiamo approfondito nella lezione sull'integrità referenziale). Supponiamo di voler trovare i clienti che hanno effettuato almeno un ordine, possiamo scrivere:

SELECT c.nome
FROM clienti AS c
WHERE EXISTS (
    SELECT 1
    FROM ordini AS o
    WHERE o.cliente_id = c.id
);

La query interna contiene c.id, che appartiene alla query esterna, perché utilizza la tabella clienti. Per questo motivo la subquery è correlata alla query esterna.

Possiamo immaginare il funzionamento in questo modo:

Verificare l'esistenza con EXISTS

L'operatore EXISTS serve per verificare se una subquery restituisce almeno una riga. Se la subquery restituisce almeno una riga, EXISTS è TRUE, se non restituisce nessuna riga, EXISTS è FALSE.

Nell'esempio precedente, abbiamo usato EXISTS:

SELECT c.nome
FROM clienti AS c
WHERE EXISTS (
    SELECT 1
    FROM ordini AS o
    WHERE o.cliente_id = c.id
);

L'utilizzo di SELECT 1 può sembrare particolare. Con EXISTS, infatti, non interessa quale valore restituisce la subquery: interessa soltanto sapere se esiste almeno una riga.

Verificare l'assenza con NOT EXISTS

NOT EXISTS funziona al contrario di EXISTS: verifica che la subquery non restituisca nessuna riga.

Possiamo utilizzarlo per trovare i clienti che non hanno effettuato ordini:

SELECT c.nome
FROM clienti AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM ordini AS o
    WHERE o.cliente_id = c.id
);

Usare IN o EXISTS

Nella lezione precedente abbiamo visto che IN può essere utilizzato con una subquery che restituisce una colonna:

SELECT c.nome
FROM clienti AS c
WHERE c.id IN (
    SELECT o.cliente_id
    FROM ordini AS o
);

Questa query può essere utilizzata per ottenere lo stesso risultato dell'esempio con EXISTS:

SELECT c.nome
FROM clienti AS c
WHERE EXISTS (
    SELECT 1
    FROM ordini AS o
    WHERE o.cliente_id = c.id
);

Le due query esprimono però il problema in modo diverso:

Valori NULL

Anche se IN ed EXISTS possono permettere di ottenere lo stesso risultato, una differenza importante riguarda il comportamento con i valori NULL con NOT IN.

Supponiamo di avere:

SELECT c.nome
FROM clienti AS c
WHERE c.id NOT IN (
    SELECT o.cliente_id
    FROM ordini AS o
);

Se la subquery può restituire NULL, il comportamento di NOT IN può diventare problematico a causa della logica di SQL (TRUE, FALSE, UNKNOWN) che abbiamo già visto nelle condizioni avanzate.

Per verificare l'assenza di una relazione, NOT EXISTS è spesso una scelta migliore:

SELECT c.nome
FROM clienti AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM ordini AS o
    WHERE o.cliente_id = c.id
);