Risposta.

Storia della domanda:

Gli operatori EXISTS e IN vengono utilizzati per filtrare i record sulla base di sottoquery. Sin dall'inizio di SQL, gli sviluppatori si sono trovati a dover scegliere tra di essi, cercando di capire quale metodo funzioni più velocemente e in quali casi sia preferibile il loro utilizzo.

Problema:

L'obiettivo principale è ottenere solo quelle righe che hanno una corrispondenza in una tabella esterna o interna, il che è sempre critico per le prestazioni con grandi insiemi di dati. La scelta tra EXISTS e IN dipende dalla struttura della sottoquery, dal numero e dall'unicità dei valori restituiti, così come dal sistema di gestione di database utilizzato.

Soluzione:

IN è generalmente più efficiente quando la sottoquery restituisce un numero ridotto di valori univoci.
EXISTS è preferibile se è importante solo l'esistenza di righe corrispondenti; è adatto per sottoquery più grandi che restituiscono migliaia e milioni di righe.
È inoltre necessario tenere presente il comportamento specifico con NULL e le differenze di ottimizzazione tra i vari sistemi di gestione di database.

Esempio di codice:

-- Utilizzo di IN
SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments WHERE course = 'SQL');

-- Utilizzo di EXISTS
SELECT name FROM students WHERE EXISTS (SELECT 1 FROM enrollments WHERE enrollments.student_id = students.id AND enrollments.course = 'SQL');

Caratteristiche chiave:

EXISTS interrompe la ricerca alla prima corrispondenza (la sottoquery annidata può fermarsi).
IN è generalmente efficace con elenchi brevi, ma può essere lento con sottoquery lunghe e non univoche.
EXISTS funziona correttamente in presenza di NULL, IN potrebbe comportarsi in modo imprevisto in caso di NULL nella sottoquery.

Domande trabocchetto.

Cosa succede se la sottoquery IN incontra NULL?

Molti credono che IN ignori semplicemente NULL, ma la presenza di NULL può rendere il risultato imprevedibile. Ad esempio, la query:

SELECT id FROM orders WHERE client_id IN (1, NULL, 2);

non includerà tecnicamente righe in cui client_id non è uguale a 1 o 2, ma se nella lista della sottoquery è presente solo NULL, il risultato sarà vuoto.

EXISTS e IN sono costrutti completamente intercambiabili?

No. L'uso di EXISTS è spesso più veloce perché non è necessario analizzare l'intera sottoquery. Inoltre, IN non funziona con sottoquery che restituiscono più colonne, mentre EXISTS sì, poiché il confronto avviene secondo la condizione nel WHERE. Ad esempio:

SELECT col1 FROM t1 WHERE (col1, col2) IN (SELECT col3, col4 FROM t2);

Questa variante è spesso non supportata, mentre l'equivalente EXISTS è.

Può IN funzionare più velocemente di EXISTS quando si lavora con campi indicizzati?

Sì, se la sottoquery è piccola e c'è un indice sul campo confrontato, IN potrebbe risultare più veloce. Tuttavia, per insiemi di dati grandi o in assenza di indice, il contrario è vero.

Errori comuni e anti-pattern

Utilizzare IN con una sottoquery che restituisce un numero elevato di righe.
Sottovalutare le conseguenze della presenza di NULL nella sottoquery.
Sostituire EXISTS con IN in tutti i casi per default senza analizzare il piano della query.

Esempi dal vivo

Caso negativo

Un analista ha costruito un report utilizzando IN, non tenendo conto che la sottoquery restituisce centinaia di migliaia di righe con NULL. Il report ha iniziato a impiegare minuti, perdendo talvolta dati.

Pro:

La logica è semplice da comprendere Contro:
Perdita di prestazioni
Rischio di ottenere dati errati

Caso positivo

La stessa query è stata riscritta utilizzando EXISTS con una condizione aggiuntiva, gli indici sono stati riguardati.