Réécrire IN, EXISTS et les sous-requêtes corrélées

Les données utilisées dans cet article — perf_sales et employee

Deux requêtes qui renvoient le même résultat peuvent quand même produire des plans de requête différents (les étapes que la base de données a choisies pour décider quelles tables lire dans quel ordre, et quels index utiliser).

Dans cet article tu apprendras quand utiliser IN par rapport à EXISTS, comment éliminer une sous-requête corrélée (une sous-requête ré-évaluée pour chaque ligne externe), et comment réécrire « ne garder que les lignes qui ont (ou n'ont pas) une correspondance » — en comparant les plans de requête avec EXPLAIN QUERY PLAN au fur et à mesure.

Avant de te lancer dans les exercices, vérifions les définitions de colonnes et les données d'exemple pour les deux tables utilisées dans cet article — perf_sales et employee.

① Utilise PRAGMA table_info(perf_sales); et PRAGMA table_info(employee); pour confirmer les définitions de colonnes des deux tables.

② Utilise SELECT * FROM perf_sales LIMIT 5; et SELECT * FROM employee LIMIT 5; pour prévisualiser les 5 premières lignes de chacune. perf_sales a 50 000 lignes, donc ajoute toujours LIMIT lors d'une prévisualisation.

IN et EXISTS — deux façons d'écrire la même vérification

Il y a deux façons de demander « y a-t-il une ligne correspondante dans une autre table ? ».

WHERE col IN (SELECT ...) vérifie si la valeur se trouve dans l'ensemble renvoyé par la requête interne, alors que WHERE EXISTS (SELECT 1 FROM ... WHERE ...) vérifie si la requête interne renvoie au moins une ligne.

Dans de nombreux cas les deux produisent le même résultat, et la base de données peut traiter l'une ou l'autre comme « ne garder que les lignes qui ont une correspondance ».

Préfixer la requête avec EXPLAIN QUERY PLAN requête; affiche les étapes que la base de données a choisies, ligne par ligne.

SCAN perf_sales signifie un parcours complet de lignes, SEARCH ... USING INDEX signifie aller directement aux lignes nécessaires via un index, et CORRELATED SCALAR SUBQUERY est une sous-requête qui est ré-évaluée pour chaque ligne externe.

-- Exemple : compter les ventes traitées par les employés du dept_id=2, de deux façons

-- Forme IN
SELECT COUNT(*) FROM perf_sales
WHERE emp_id IN (SELECT emp_id FROM employee WHERE dept_id = 2);

-- Forme EXISTS (corrélée)
SELECT COUNT(*) FROM perf_sales p
WHERE EXISTS (
  SELECT 1 FROM employee e
  WHERE e.emp_id = p.emp_id AND e.dept_id = 2
);

-- Préfixe avec EXPLAIN QUERY PLAN pour voir le plan
-- EXPLAIN QUERY PLAN SELECT COUNT(*) FROM perf_sales WHERE ...;

Soyons plus concrets. Comment IN et EXISTS se comportent-ils selon la taille de la table externe et la taille de la sous-requête interne et l'existence d'un index ?

Les deux cas ci-dessous — et quelle forme est la plus rapide dans chacun — sont résumés dans le schéma qui suit.

-- Cas A : externe perf_sales (50 000 lignes), interne employés dept_id=1 (~5 lignes, petit et fixe)

-- Forme IN
SELECT * FROM perf_sales
WHERE emp_id IN (SELECT emp_id FROM employee WHERE dept_id = 1);

-- Forme EXISTS
SELECT * FROM perf_sales p
WHERE EXISTS (
  SELECT 1 FROM employee e
  WHERE e.emp_id = p.emp_id AND e.dept_id = 1
);

-- Cas B : externe employee (30 lignes), interne perf_sales (50 000 lignes, avec un index sur emp_id)

-- Forme IN
SELECT * FROM employee e
WHERE e.emp_id IN (SELECT emp_id FROM perf_sales);

-- Forme EXISTS
SELECT * FROM employee e
WHERE EXISTS (
  SELECT 1 FROM perf_sales p
  WHERE p.emp_id = e.emp_id
);

Conseil — les bases modernes ne montrent presque aucune vraie différence de vitesse

Nous avons trié les choses en « IN tend à gagner / EXISTS tend à gagner », mais c'est une tendance enracinée dans des optimiseurs plus anciens.

Dans les bases modernes comme SQLite, PostgreSQL et MySQL 8.0+, à la fois IN (SELECT ...) et un EXISTS corrélé se font réécrire vers le même plan « ne garder que les lignes qui ont une correspondance », et le plan réel et la vitesse en pratique finissent à peu près identiques.

Autrement dit, tu peux choisir celle qui se lit mieux.

Quand la différence de plan ou de vitesse importe vraiment, la règle est de mesurer — EXPLAIN QUERY PLAN et temps d'horloge.

Écris « le nombre de perf_sales traités par les employés avec dept_id 2 » sous deux formes — IN et EXISTS — et compare chaque plan de requête. (Exécute-le correctement pour révéler l'explication.)

① Préfixe avec EXPLAIN QUERY PLAN pour afficher le plan de la requête de comptage sous la forme emp_id IN (SELECT ...).

② Puis affiche le plan pour le même comptage écrit sous forme EXISTS (en corrélant contre employee) — encore avec EXPLAIN QUERY PLAN.

③ Compare les deux plans, en regardant les mots SCAN / SEARCH / USING INDEX et ainsi de suite.

Réécrire une sous-requête corrélée en JOIN + agrégation

Une sous-requête corrélée dans la liste SELECT (une ré-évaluée par ligne externe) se fait appeler de plus en plus souvent à mesure que le nombre de lignes grandit.

Dans EXPLAIN QUERY PLAN elle apparaît comme une ligne CORRELATED SCALAR SUBQUERY.

Dans de nombreux cas elle peut être réécrite comme une seule agrégation préparée à l'avance et jointe — la ré-évaluation par ligne disparaît.

Le motif de réécriture transforme « pour chaque ligne externe, appeler (SELECT ... WHERE child.key = parent.key) » en « construire la table d'agrégation une fois avec GROUP BY key, puis JOIN à la parente ».

Le résultat est le même et la corrélation disparaît du plan.

-- Avant : total des ventes par employé via une sous-requête corrélée
SELECT e.emp_id, e.name,
  (SELECT SUM(p.amount) FROM perf_sales p
   WHERE p.emp_id = e.emp_id) AS total
FROM employee e
ORDER BY e.emp_id;

-- Après : construire l'agrégation une fois et JOIN
WITH sales_sum AS (
  SELECT emp_id, SUM(amount) AS total
  FROM perf_sales
  GROUP BY emp_id
)
SELECT e.emp_id, e.name, s.total
FROM employee e
LEFT JOIN sales_sum s ON s.emp_id = e.emp_id
ORDER BY e.emp_id;

Exécute « une liste du total de perf_sales de chaque employé » de deux façons — comme sous-requête corrélée et comme réécriture — et regarde comment le plan change.

① Préfixe avec EXPLAIN QUERY PLAN pour afficher le plan de la version qui met (SELECT SUM(amount) FROM perf_sales WHERE même emp_id) dans la liste SELECT comme sous-requête corrélée.

② Roule la même agrégation dans un CTE qui fait GROUP BY sur emp_id une fois, LEFT JOIN-le à employee, et affiche ce plan aussi avec EXPLAIN QUERY PLAN.

③ Confirme que la ligne CORRELATED SCALAR SUBQUERY de la version corrélée disparaît dans la version réécrite.

Le piège NULL de NOT IN — évite-le avec NOT EXISTS

Quand tu demandes « aucune ligne correspondante » — une anti-jointure — NOT IN (SELECT ...) a un piège.

Si même un seul NULL se glisse dans l'ensemble interne, la logique à trois valeurs (TRUE / FALSE / UNKNOWN) rend la condition entière UNKNOWN, et le résultat revient avec 0 ligne.

En revanche, NOT EXISTS (SELECT 1 ... WHERE ...) demande seulement si l'interne produit une ligne, donc NULL ne le casse pas et l'anti-jointure fonctionne correctement.

Quand tu écris une anti-jointure, utilise NOT EXISTS — ou, si tu dois utiliser NOT IN, ajoute WHERE col IS NOT NULL à l'intérieur pour éliminer les NULL.

Ce cours utilise NOT EXISTS comme forme standard.

-- Construis un exemple avec NULL dans l'ensemble interne, en utilisant team
-- team(mgr_emp) inclut délibérément un NULL
WITH team(mgr_emp) AS (
  VALUES (3), (7), (NULL)
)
-- Piège : avec NULL présent, NOT IN renvoie 0 ligne
SELECT COUNT(*) FROM perf_sales
WHERE emp_id NOT IN (SELECT mgr_emp FROM team);

-- Correction : NOT EXISTS n'est pas affecté par NULL, donc l'anti-jointure fonctionne
WITH team(mgr_emp) AS (
  VALUES (3), (7), (NULL)
)
SELECT COUNT(*) FROM perf_sales p
WHERE NOT EXISTS (
  SELECT 1 FROM team t WHERE t.mgr_emp = p.emp_id
);

Observe — via les comptes de lignes — ce qui se passe quand l'ensemble interne contient un NULL, en comparant NOT IN et NOT EXISTS. C'est le dernier exercice de l'article. Construis une petite liste de valeurs appelée team (trois valeurs : 3, 7, NULL) comme CTE, puis compte « les lignes perf_sales dont emp_id n'est pas dans cette liste » de deux façons.

① Construis un CTE avec 3, 7, NULL en utilisant WITH team(mgr_emp) AS (VALUES ...), et compte les lignes avec emp_id NOT IN (SELECT mgr_emp FROM team).

② Écris la même vérification comme NOT EXISTS (SELECT 1 FROM team t WHERE t.mgr_emp = p.emp_id) et compte les lignes.

③ Compare les deux comptes et confirme que la version NOT IN est 0.