Risposta.

Storia della domanda

Spesso è necessario formare query in cui i criteri di filtraggio sono determinati dinamicamente: l'elenco di valori è fornito dall'utente dell'interfaccia e i valori mancanti (NULL) devono poter comparire o meno nel risultato. Questa query deve funzionare rapidamente su grandi volumi e deve essere corretta dal punto di vista semantico SQL.

Problema

La combinazione scorretta delle condizioni IN, NOT IN e la verifica su NULL porta a risultati inaspettati poiché in SQL l'espressione "NULL IN (...)" restituisce sempre UNKNOWN, e "NOT IN" può portare a un risultato vuoto se almeno uno degli elementi dell'elenco è NULL. Le prestazioni su grandi dataset possono deteriorarsi drasticamente con condizioni scritte in modo inefficace.

Soluzione

La combinazione corretta appare così:

Se selezioniamo valori dall'elenco, aggiungiamo una condizione su NULL se dobbiamo restituirli:

SELECT * FROM sales
WHERE region IN ('Moscow','Samara') OR region IS NULL;

Per la negazione utilizziamo una logica esplicita:

SELECT * FROM sales
WHERE (region NOT IN ('Moscow','Samara') OR region IS NULL);

Per escludere severamente i valori mancanti:

SELECT * FROM sales
WHERE region NOT IN ('Moscow','Samara') AND region IS NOT NULL;

Per grandi tabelle, spesso è fondamentale coprire gli indici, specialmente se il filtro su un elenco è un'operazione comune.

Caratteristiche chiave:

Gestire esplicitamente il filtraggio su NULL, altrimenti perdiamo righe.
L'uso corretto di IN e NOT IN è critico per selezioni corrette.
L'indicizzazione sui campi coinvolti in IN/NOT IN accelera notevolmente il filtro.

Domande trabocchetto.

Cosa succede se si verifica "WHERE field NOT IN ('a', NULL)"?

Il risultato è sempre vuoto, poiché qualsiasi riga viene confrontata con NULL nell'elenco, dando UNKNOWN e filtrando tutto.

Esempio di codice:

SELECT * FROM test WHERE name NOT IN ('Ivan', NULL);

Restituirà "IN (NULL,...)" un valore per righe con NULL?

No. L'espressione NULL IN (...) restituisce sempre UNKNOWN, e non TRUE.

Come accelerare il filtraggio su un grande elenco di valori?

Utilizzare indici e preferibilmente applicare JOIN con una tabella temporanea, se l'elenco è ampio (centinaia/migliaia di valori) — questo accelera il filtraggio rispetto a un lungo IN.

Errori tipici e anti-pattern

Scrivere solo IN/NOT IN senza una condizione ESPLICITA su NULL
Non considerare le peculiarità dell'esecuzione di NOT IN con NULL
Non ottimizzare l'indicizzazione per il campo di filtraggio

Esempio dalla vita reale

Caso negativo

Nel rapporto analitico è stata applicata "WHERE city NOT IN ('London', field_2, NULL)". Risultato finale: nessun risultato, nessuno riusciva a capire a lungo il motivo della perdita di righe.

Pro:

Query breve e "leggibile"

Contro:

Perdita di dati, logica non ovvia per gli sviluppatori

Caso positivo

Nel CRM è stato scelto un elenco di regioni clienti e aggiunto un pulsante aggiuntivo "Includi valori non compilati". La logica di filtraggio:

... WHERE city IN ('SPB','NNov') OR city IS NULL