Réponse.

Historique de la question :

Les opérateurs EXISTS et IN sont utilisés pour filtrer des enregistrements basés sur des sous-requêtes. Depuis l'apparition de SQL, les développeurs se sont confrontés au choix entre ces deux options, tentant de comprendre laquelle fonctionne plus rapidement et dans quels cas leur utilisation est préférable.

Problématique :

La tâche principale est de n'obtenir que les lignes qui ont des correspondances dans une table externe ou interne, ce qui est toujours critique pour la performance avec de grands ensembles de données. Le choix entre EXISTS et IN dépend de la structure de la sous-requête, du nombre et de l'unicité des valeurs retournées, ainsi que du SGBD utilisé.

Solution :

IN est généralement plus efficace lorsque la sous-requête retourne un petit nombre de valeurs uniques.
EXISTS est préférable lorsque seul le fait de l'existence de lignes correspondantes est important ; il convient aux grandes sous-requêtes retournant des milliers et des millions de lignes.
Il faut également se souvenir du comportement spécifique avec NULL et des différences d'optimisation entre les différents systèmes de gestion de bases de données.

Exemple de code :

-- Utilisation de IN
SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments WHERE course = 'SQL');

-- Utilisation de EXISTS
SELECT name FROM students WHERE EXISTS (SELECT 1 FROM enrollments WHERE enrollments.student_id = students.id AND enrollments.course = 'SQL');

Caractéristiques clés :

EXISTS termine la recherche dès la première correspondance (la sous-requête imbriquée peut s'arrêter).
IN est généralement efficace avec des listes courtes, mais peut être lent avec des sous-requêtes longues non uniques.
EXISTS fonctionne correctement en présence de NULL, IN peut se comporter de manière inattendue en cas de NULL dans la sous-requête.

Questions pièges.

Que se passe-t-il si NULL apparaît dans la sous-requête IN ?

Beaucoup pensent qu'IN ignorera simplement NULL, mais en présence de NULL, le résultat peut devenir imprévisible. Par exemple, la requête :

SELECT id FROM orders WHERE client_id IN (1, NULL, 2);

n'inclura techniquement pas les lignes où client_id n'est pas égal à 1 ou 2, mais si seule la valeur NULL figure dans la liste de la sous-requête, le résultat sera vide.

EXISTS et IN sont-ils des constructions entièrement interchangeables ?

Non. L'utilisation de EXISTS est souvent plus rapide, car il n'est pas nécessaire d'analyser l'ensemble de la sous-requête. De plus, IN ne fonctionne pas avec des sous-requêtes de plusieurs colonnes, alors qu'EXISTS le fait, car la comparaison se fait selon la condition dans WHERE. Par exemple :

SELECT col1 FROM t1 WHERE (col1, col2) IN (SELECT col3, col4 FROM t2);

Cette option est souvent non supportée, tandis que l'EXISTS équivalent l'est.

IN peut-il être plus rapide qu'EXISTS lors de la manipulation de champs indexés ?

Oui, si la sous-requête est petite et que le champ comparé est indexé, IN peut être plus rapide. Cependant, avec de grands ensembles ou en l'absence d'index, c'est l'inverse.

Erreurs typiques et anti-patterns

Utilisation de IN avec une sous-requête retournant un grand nombre de lignes.
Négliger les conséquences de la présence de NULL dans la sous-requête.
Remplacer EXISTS par IN par défaut sans analyser le plan de requête.

Exemple de la vie réelle

Cas négatif

Un analyste a construit un rapport avec la méthode IN, sans tenir compte que la sous-requête retourne des centaines de milliers de lignes avec NULL. Le rapport a mis des minutes à s'exécuter, perdant parfois des données.

Avantages :

La logique est simple à comprendre Inconvénients :
Perte de performance
Risque d'obtenir des données incorrectes

Cas positif

La même requête a été réécrite avec EXISTS avec une condition supplémentaire, les index ont été recalculés.