EXISTS et sous-requêtes corrélées

Données utilisées dans cet article — employee / sales / department

EXISTS est une sous-requête qui vérifie uniquement s'il existe au moins une ligne correspondante, et tu l'utilises un peu comme IN.

Dans cet article, tu vas parcourir les bases de EXISTS / NOT EXISTS, comment éviter sûrement le piège NOT IN + NULL en l'écrivant avec EXISTS, et comment choisir entre EXISTS et IN selon la lisibilité.

Le matériel est constitué de trois tables de données d'employés.

En utilisant employee (30 employés), sales (50 lignes de ventes) et department (6 services), tu vas extraire « les employés avec / sans ventes » et « les services sans employés ».

Avant de plonger dans les exercices, regarde les définitions de colonnes et les données d'exemple des trois tables utilisées dans cet article — employee / sales / department.

① Exécute PRAGMA table_info(...) pour vérifier les définitions de colonnes des trois tables.

② Exécute SELECT * FROM nom_table LIMIT 5; pour prévisualiser les 5 premières lignes de chaque table. Le fait que employee.dept_id puisse être NULL revient aussi dans un exercice ultérieur.

EXISTS / NOT EXISTS — vérifier seulement si une ligne existe

WHERE EXISTS (SELECT ... ) est vrai si la sous-requête renvoie ne serait-ce qu'une ligne, et faux si elle ne renvoie aucune ligne du tout.

Ce que la sous-requête sélectionne n'affecte pas le résultat, donc par convention tu écris SELECT 1 (la valeur n'est pas utilisée — seule compte l'existence d'une ligne).

La forme de base est une sous-requête corrélée qui référence une colonne de la requête principale à l'intérieur du EXISTS.

« Les employés avec des ventes » est WHERE EXISTS (SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id), et « les employés sans ventes » est la négation de cela, NOT EXISTS.

EXISTS arrête l'évaluation dès qu'il trouve une ligne, ce qui en fait un bon choix pour les tests d'existence.

-- EXISTS : employés avec des ventes
SELECT emp.emp_id, emp.name
FROM employee emp
WHERE EXISTS (
  SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id
)
ORDER BY emp.emp_id;

Imagine le besoin « je veux une liste des employés qui n'apparaissent même pas une fois dans les lignes de ventes (les employés qui n'ont pas encore réalisé de ventes) ». (Exécute-le correctement et l'explication s'affiche.)

① Donne à la table employee l'alias e et extrais emp_id et name.

② Utilise NOT EXISTS pour ne garder que les lignes où cet employé n'a aucune ligne du tout dans sales. Corrèle la sous-requête avec la requête principale sous la forme SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id.

③ Trie par emp_id en ordre croissant.

Le piège NOT IN + NULL — EXISTS est sûr

Si tu écris un « filtrage négatif » avec NOT IN, le résultat peut revenir à 0 ligne quand un NULL se glisse du côté de la sous-requête.

x NOT IN (1, 2, NULL) est évalué comme « x n'est pas 1, pas 2, et pas NULL », mais x <> NULL ne devient jamais vrai et est toujours NULL (inconnu), donc le NOT IN entier ne devient jamais vrai.

Le raisonnement complet est expliqué dans Sous-requêtes ① (le piège NOT IN + NULL).

Comme employee.dept_id mêle des NULL, écrire « les services sans employés » sous la forme WHERE dept_id NOT IN (SELECT dept_id FROM employee) fait disparaître Legal (un service sans aucun employé), qui devrait pourtant être là, et le résultat revient à 0 ligne.

NOT EXISTS regarde uniquement si une ligne existe et n'est pas affecté par NULL, donc dans cette situation NOT EXISTS renvoie le résultat correct.

-- Piège : une sous-requête contenant NULL + NOT IN revient à 0 ligne
SELECT dept_id, dept_name
FROM department
WHERE dept_id NOT IN (SELECT dept_id FROM employee);
-- → 0 ligne car employee.dept_id contient NULL

-- Sûr : NOT EXISTS n'est pas affecté par NULL
SELECT d.dept_id, d.dept_name
FROM department d
WHERE NOT EXISTS (
  SELECT 1 FROM employee emp WHERE emp.dept_id = d.dept_id
);

Imagine le besoin « je veux lister les services auxquels aucun employé n'est affecté ». Comme employee.dept_id mêle des NULL, écris-le sûrement avec NOT EXISTS.

① Donne à la table department l'alias d et extrais dept_id et dept_name.

② Utilise NOT EXISTS pour ne garder que les lignes où ce service n'a aucune ligne d'employé du tout dans employee. Corrèle la sous-requête sous la forme SELECT 1 FROM employee emp WHERE emp.dept_id = d.dept_id.

③ Trie par dept_id en ordre croissant.

Vérifie ce qui se passe quand tu écris l'exercice précédent avec NOT IN. Cet exercice est censé te laisser vivre l'erreur par toi-même, donc exécute-le en NOT IN et observe que le résultat revient à 0 ligne.

① Extrais dept_id et dept_name de la table department.

② Essaie de lister « les services sans employés » en utilisant WHERE dept_id NOT IN (SELECT dept_id FROM employee).

③ Exécute-le et confirme que le résultat est de 0 ligne (le service Legal ne ressort pas). Réfléchis à pourquoi le résultat diffère de la version NOT EXISTS de l'Exercice 2.

Choisis EXISTS selon la lisibilité

EXISTS regarde uniquement si une ligne existe, donc il est sûr même en présence de NULL, mais écrire une sous-requête corrélée peut le faire paraître plus long que IN.

Pour un simple test d'existence, il y a des situations où IN se lit mieux, et en pratique tu choisis selon le contexte.

En revanche, pour une négation comme NOT IN où NULL casse le résultat, ou quand tu veux juger de façon fiable seulement si une ligne existe, EXISTS / NOT EXISTS est le choix sûr.