Les élèves sans note, et le piège de NOT IN

Exercice · niveau 2 · informatique (MP2I/MPI), chapitre 21 — Bases de données relationnelles et SQL

Énoncé

Écrire trois requêtes rendant les élèves sans aucune note. Puis expliquer pourquoi celle-ci n'en rend aucun :


SELECT nom FROM Eleve
WHERE id NOT IN (SELECT n.eleve FROM Eleve e LEFT JOIN Note n ON n.eleve = e.id);

Corrigé

Les trois écritures, qui rendent toutes Dupont :


-- (a) la jointure externe, puis l'absence de correspondant
SELECT e.nom FROM Eleve e LEFT JOIN Note n ON n.eleve = e.id
WHERE n.eleve IS NULL;

-- (b) la requête imbriquée
SELECT nom FROM Eleve WHERE id NOT IN (SELECT eleve FROM Note);

-- (c) l'opérateur ensembliste (rend l'identifiant, pas le nom)
SELECT id FROM Eleve EXCEPT SELECT eleve FROM Note;

La forme (a) mérite un commentaire : on teste n.eleve IS NULL et non n.valeur IS NULL. La différence compte dès qu'une note peut elle-même être NULL : c'est la clé de la table de droite qui atteste l'absence de correspondant, jamais un attribut ordinaire.

Et le piège. La requête de l'énoncé rend zéro ligne, mesuré. Regardons ce que rend sa sous-requête :

`SELECT n.eleve FROM Eleve e LEFT JOIN Note n ON n.eleve = e.id`
$1, 1, 2, 2, 3, NULL$

La jointure externe a fabriqué un NULL pour Dupont. Or x NOT IN (a, b, NULL) se déplie en

$NOT (x = a OR x = b OR x = NULL) .$

Le dernier terme vaut inconnu. Une disjonction dont un terme est inconnu et les autres faux vaut inconnu ; sa négation aussi. La condition n'est jamais vraie : aucune ligne ne sort.

Attention`NOT IN` sur un ensemble contenant `NULL` rend toujours vide

C'est le défaut le plus vicieux de SQL, parce qu'il est silencieux et qu'il dépend des données : la même requête marche tant qu'aucun NULL n'entre dans la sous-requête, puis cesse de marcher le jour où il en arrive un. Le symptôme est un résultat vide, jamais une erreur.

Noter l'asymétrie : IN n'a pas ce défaut. x IN (a, NULL) vaut vrai dès que $x = a$ . C'est bien la négation qui casse — comme à l'exercice 21.2.

La réparation est de filtrer les NULL dans la sous-requête, ou — mieux — d'employer la forme (a), qui n'a jamais ce problème :


SELECT nom FROM Eleve
WHERE id NOT IN (SELECT n.eleve FROM Eleve e LEFT JOIN Note n ON n.eleve = e.id
                 WHERE n.eleve IS NOT NULL);   -- rend bien Dupont

Les autres exercices de ce chapitre Le cours du chapitre

Un blocage sur cet exercice ? Le tuteur d'Adloun guide par questions, sans donner la réponse.