Réponse.

Historique de la question

Il est souvent nécessaire de formuler des requêtes où les critères de filtrage sont définis dynamiquement — la liste des valeurs est spécifiée par l'utilisateur de l'interface, et les valeurs manquantes (NULL) doivent également être en mesure de figurer ou non dans le résultat. Une telle requête doit fonctionner rapidement sur de grands volumes et être correcte du point de vue de la sémantique SQL.

Problème

Une combinaison incorrecte des conditions IN, NOT IN et vérification de NULL conduit à des résultats inattendus car, dans SQL, l'expression "NULL IN (...)" retourne toujours UNKNOWN, et "NOT IN" peut entraîner un résultat vide si au moins un des éléments de la liste est NULL. La performance sur de grandes données peut chuter brusquement si la condition est mal écrite.

Solution

Une bonne combinaison ressemble à ceci :

Si nous choisissons des valeurs dans la liste, nous complétons la condition par NULL si nous voulons également les retourner :

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

Pour la négation, nous utilisons une logique explicite :

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

Pour exclure strictement les manquants :

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

Pour les grandes tables, des index sont souvent pertinents, surtout si le filtre par liste est une opération fréquente.

Caractéristiques clés :

Gérer explicitement le filtrage par NULL, sinon nous perdons des lignes.
L'utilisation correcte de IN et NOT IN est critique pour les sélections correctes.
L'indexation sur les champs participant à IN/NOT IN accélère considérablement le filtrage.

Questions pièges.

Que se passera-t-il si nous vérifions "WHERE field NOT IN ('a', NULL)"?

Le résultat est toujours vide, car toute ligne est comparée à NULL dans la liste, ce qui donne UNKNOWN et filtre tout.

Exemple de code :

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

L'expression "IN (NULL,...)" donnera-t-elle une valeur pour les lignes avec NULL?

Non. L'expression NULL IN (...) retourne toujours UNKNOWN, et non TRUE.

Comment accélérer le filtrage sur une grande liste de valeurs?

Utiliser des index et de préférence appliquer JOIN avec une table temporaire si la liste est grande (centaines/ milliers de valeurs) — cela accélère le filtrage par rapport à un long IN.

Erreurs typiques et anti-patterns

Écrire uniquement IN/NOT IN sans condition EXPLICITE sur NULL
Ne pas tenir compte des particularités de l'exécution de NOT IN avec NULL
Ne pas optimiser l'indexation pour le champ de filtrage

Exemple de la vie réelle

Cas négatif

Dans un rapport analytique, on a appliqué "WHERE city NOT IN ('Londres', field_2, NULL)". Résultat — total vide, personne ne pouvait comprendre pourquoi des lignes étaient perdues.

Avantages :

Requête courte et "lisible"

Inconvénients :

Perte de données, logique non évidente pour les développeurs

Cas positif

Dans le CRM, une liste de régions clients a été sélectionnée et un bouton supplémentaire "Inclure les valeurs non renseignées" a été ajouté. Logique de filtrage :

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