Piège NOT IN et NULL
NOT IN renvoie zéro ligne dès que sa sous-requête contient un NULL, sans aucun message d'erreur. Pour chercher des éléments absents d'une autre table, NOT EXISTS donne toujours le bon résultat.
- NOT IN
- NULL
- NOT EXISTS
- anti-jointure
- piège
Syntaxe
-- À éviter si la sous-requête peut contenir NULLWHERE col NOT IN (SELECT autre_col FROM t2)-- Forme sûreWHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.autre_col = t1.col)Exemple
SELECT nameFROM employeesWHERE id NOT IN (SELECT manager_id FROM employees)ORDER BY name;SELECT e.nameFROM employees eWHERE NOT EXISTS ( SELECT 1 FROM employees s WHERE s.manager_id = e.id)ORDER BY e.name;-- Jeu de données : entreprise-- Objectif : les employés qui ne managent personne.-- La première requête renvoie 0 ligne, car manager_id vaut NULL pour la directrice.-- Résultat attendu (seconde requête, NOT EXISTS) :-- Quentin Faure-- Rachid Amrani-- Théo Brun-- Ursula Klein-- Victor Nguyen-- Wendy RocheÀ retenir
- Pourquoi : id NOT IN (1, 2, NULL) équivaut à id <> 1 AND id <> 2 AND id <> NULL. Le dernier test vaut NULL, donc la condition entière n'est jamais vraie.
- Trois corrections équivalentes : NOT EXISTS, ajouter WHERE manager_id IS NOT NULL dans la sous-requête, ou LEFT JOIN … WHERE s.id IS NULL. EXCEPT fonctionne aussi, car il traite les NULL comme égaux.
- Piège : la requête peut fonctionner pendant des mois, puis renvoyer soudain un résultat vide le jour où un NULL entre dans la colonne. C'est le même comportement dans tous les SGBD, car c'est la norme SQL.
- IN, lui, n'est pas touché : une valeur présente dans la liste est trouvée même si la liste contient aussi NULL.
Voir aussi
- EXISTS & IN (sous-requête)EXISTS vérifie qu'une sous-requête renvoie au moins une ligne, IN qu'une valeur figure dans sa liste. Tous deux répondent à « a-t-il au moins un… ? » sans dupliquer les lignes comme le ferait un JOIN.
- IS NULL / IS NOT NULLNULL signifie « valeur inconnue » et ne se compare pas avec =. IS NULL et IS NOT NULL sont les seuls tests fiables pour trouver une donnée manquante, comme une classe sans professeur.
- INTERSECT & EXCEPTINTERSECT garde les lignes présentes dans les deux requêtes, EXCEPT celles de la première absentes de la seconde. Idéal pour répondre à « qui est dans cette liste mais pas dans l'autre ? ».
- LEFT JOINLEFT JOIN garde toutes les lignes de la table de gauche, avec ou sans correspondance à droite (les colonnes de droite valent alors NULL). Idéal pour compter des zéros ou repérer des absents.