NOT IN vs NOT EXISTS en SQL : pourquoi le résultat diffère

"Trouver les X qui ne sont pas dans Y."

C'est une question qui revient en permanence quand tu fais de la data comme par exemple les clients qui n'ont pas passé de commande, les employés sans projet affecté, les produits jamais vendus, les utilisateurs sans login depuis 30 jours, les lignes orphelines après un MERGE.

Trois écritures SQL répondent à cette question. Elles paraissent équivalentes, et la plupart des tutos les présentent comme interchangeables. Sauf qu'elles ne le sont pas. Et la différence vient d'un seul concept que beaucoup oublient, la manière dont SQL gère les NULL.

Si tu as déjà eu une requête NOT IN qui renvoie zéro ligne sans raison apparente, ou un anti-join qui "marche" en dev et "ne marche plus" en prod après ajout d'une colonne nullable, c'est ce dont on va parler.

Setup : un cas tout simple

Deux tables, format classique :

-- Table clients
| id | nom    |
|----|--------|
| 1  | Alice  |
| 2  | Bob    |
| 3  | Chloé  |
| 4  | David  |

-- Table commandes
| id  | client_id |
|-----|-----------|
| 101 | 1         |
| 102 | 2         |
| 103 | NULL      |   -- (client supprimé, import partiel...)

Objectif : trouver les clients qui n'ont jamais passé de commande. Réponse attendue : Chloé et David.

Cette colonne client_id nullable dans commandes, ça arrive tout le temps en vrai, soit parce que la donnée vient d'un import sale, soit parce que la modélisation autorise les valeurs manquantes. Et c'est elle qui va faire la différence.

Approche 1 : NOT IN

L'écriture la plus intuitive :

SELECT *
FROM clients
WHERE id NOT IN (SELECT client_id FROM commandes);

Tu exécutes... et tu obtiens zéro ligne. Pas Chloé, pas David, rien.

Pourtant la requête a l'air correcte. Que se passe-t-il ?

En SQL, x NOT IN (1, 2, NULL) est équivalent à x <> 1 AND x <> 2 AND x <> NULL. Or x <> NULL ne renvoie pas FALSE, il renvoie UNKNOWN. Et un AND qui contient un UNKNOWN ne peut jamais être TRUE. Donc aucune ligne ne sort.

Pour Chloé (id = 3), le moteur évalue :

3 NOT IN (1, 2, NULL)
= 3 <> 1 AND 3 <> 2 AND 3 <> NULL
= TRUE   AND TRUE    AND UNKNOWN
= UNKNOWN

Un WHERE ne garde que les lignes dont l'expression vaut TRUE. UNKNOWN est rejeté, exactement comme FALSE. Chloé est éliminée. Même chose pour David.

Résultat : un seul NULL dans la sous-requête fait disparaître l'intégralité du résultat. C'est un grand classique des bugs en prod qui n'apparaissent que des mois après la mise en place de la requête quand une ligne NULL arrive dans la table cible.

Approche 2 : NOT EXISTS

Même question, écriture différente :

SELECT *
FROM clients c
WHERE NOT EXISTS (
  SELECT 1
  FROM commandes co
  WHERE co.client_id = c.id
);

Résultat : Chloé et David. Correct.

Pourquoi ça marche cette fois ? Parce que NOT EXISTS ne fait pas de comparaison globale avec une liste, il regarde simplement s'il existe au moins une ligne qui satisfait la condition. Pour Chloé, le moteur cherche une ligne dans commandes avec client_id = 3. La ligne avec client_id = NULL donne NULL = 3 → UNKNOWN, donc elle ne correspond pas. Aucune ligne ne correspond, donc NOT EXISTS vaut TRUE. Chloé sort dans le résultat.

Le UNKNOWN issu de la comparaison avec NULL est traité localement, ligne par ligne, dans la sous-requête. Il n'empoisonne pas le résultat global.

Approche 3 : LEFT JOIN ... WHERE ... IS NULL

Le pattern "anti-join" historique, avant que NOT EXISTS ne soit bien optimisé partout :

SELECT c.*
FROM clients c
LEFT JOIN commandes co ON co.client_id = c.id
WHERE co.client_id IS NULL;

Résultat : Chloé et David. Correct aussi.

L'idée est différente : on joint tous les clients à leurs commandes, puis on ne garde que les clients qui n'ont aucun match (donc dont la colonne issue de la jointure est NULL). Là encore, le NULL dans commandes.client_id ne provoque pas de jointure parasite avec Chloé ou David.

La logique de comparaison

SQL ne fonctionne pas avec deux valeurs de vérité (TRUE et FALSE), mais avec trois : TRUE, FALSE et UNKNOWN.

Toutes les comparaisons impliquant un NULL renvoient UNKNOWN, pas FALSE. Et la différence entre les deux est énorme.

Quelques exemples :

Et la table de vérité du AND :

Tu remarques que FALSE AND UNKNOWN = FALSE, mais TRUE AND UNKNOWN = UNKNOWN. C'est exactement ce qui se passe avec NOT IN : la première condition est TRUE, la deuxième aussi, la troisième est UNKNOWN, donc le tout devient UNKNOWN.

Une fois que tu as cette logique en tête, tu comprends pourquoi IN ne pose pas autant de problèmes que NOT IN :

• 5 IN (1, 5, NULL) => la deuxième comparaison renvoie TRUE, et un OR avec un TRUE reste TRUE. La ligne sort.
• 5 NOT IN (1, 2, NULL) => la dernière comparaison renvoie UNKNOWN, et un AND avec un UNKNOWN ne peut pas valoir TRUE. La ligne ne sort jamais.

C'est pour ça qu'on dit que NOT IN "se comporte mal" avec les NULL, et qu'IN n'a pas le même problème.

La règle pratique

Utilise NOT EXISTS par défaut. Garde NOT IN seulement si tu es sûr que ni la valeur à gauche ni les valeurs à droite ne peuvent être NULL, typiquement une liste codée en dur sans NULL. Les optimiseurs modernes savent souvent transformer NOT EXISTS ou LEFT JOIN ... IS NULL en anti-join physique, selon le moteur, les index et les statistiques. Mais ils doivent garder la sémantique de NOT IN donc si des NULL sont possibles, le piège reste là.

Aller plus loin : pratiquer SQL

Les anti-joins, le comportement des NULL, les sous-requêtes corrélées, c'est le genre de sujet qui ne s'apprend qu'en écrivant des requêtes et en se prenant les pièges en face pas en tuto ou vidéos. Donc lire un article ne suffit pas, il faut écrire du SQL.

Sur DataCertification.fr, le module "Sous-requêtes et EXISTS" couvre exactement ces cas (anti-joins, corrélation, gestion des NULL) avec un éditeur SQL intégré au navigateur et une validation automatique. Tu écris la requête, tu la passes, et tu vois immédiatement si elle se comporte bien dans les cas piégeux.

👉 Pratiquer ces patterns sur DataCertification.fr

Si tu travailles déjà sur des cas plus complexes (modélisation, qualité de données, déduplication, SCD, incrémental), le Lab Analytics Engineering va plus loin et applique tout ça à des pipelines complets.

Tu veux que je t'accompagne sur un projet data (modélisation, performance SQL, Snowflake, dbt, gouvernance) ? 👉 Réserver un appel de 30 minutes

Questions :