Réponse.

Historique de la question :

Les sous-requêtes imbriquées ont été initialement conçues en SQL pour étendre les capacités expressives du langage et résoudre des problèmes commerciaux complexes qui ne rentrent pas dans des opérateurs SELECT simples. Cependant, avec l'augmentation des volumes de données et la complexité des modèles relationnels, est venue la compréhension que les sous-requêtes imbriquées ne fonctionnent pas toujours efficacement : cela dépend beaucoup de l'implémentation de l'optimiseur de SGBD spécifique.

Problème :

Le principal défi est de trouver un compromis entre la lisibilité, la logique correcte et la performance. Les sous-requêtes imbriquées ne sont pas toujours optimisées en opérations de jointure, mais se transforment souvent en boucles coûteuses (Nested Loops).

Solution :

Utiliser des sous-requêtes imbriquées dans SELECT pour calculer des agrégats ou des expressions complexes, si aucune autre méthode ne peut être appliquée.
Pour de grands volumes de données, passer à JOIN — cela permet à l'optimiseur de rendre la requête basée sur un ensemble de données et d'appliquer des index.
Extraire la sous-requête vers la partie externe (via WITH/CTE), si l'on doit améliorer la lisibilité ou la performance.

Exemple de code :

-- Sous-requête imbriquée dans SELECT (avec précaution!)
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.client_id = clients.id) AS order_count
FROM clients;

-- Équivalent via JOIN (généralement plus rapide) :
SELECT clients.name, COUNT(orders.id) AS order_count
FROM clients
LEFT JOIN orders ON orders.client_id = clients.id
GROUP BY clients.name;

Caractéristiques clés :

Les sous-requêtes corrélées imbriquées entraînent souvent une performance de O(N*M)
Les sous-requêtes non corrélées sont plus sûres et plus rapides
L'extraction de la sous-requête dans WITH/CTE ou JOIN augmente la prévisibilité du plan et accélère l'exécution

Questions piégeuses.

La sous-requête imbriquée dans SELECT fonctionne-t-elle plus vite qu'un JOIN LEFT équivalent ?

Le plus souvent — non. Une sous-requête corrélée dans SELECT s'exécute pour chaque ligne de la requête externe, alors que JOIN est construit une seule fois avec des index pour toute la table.

Peut-on utiliser une sous-requête dans FROM au lieu de CTE (WITH), et y aura-t-il une différence ?

Oui, une sous-requête dans FROM :

SELECT t1.id, sub.agg
FROM table1 t1
JOIN (SELECT id, MAX(val) AS agg FROM table2 GROUP BY id) sub ON t1.id = sub.id;

Mais CTE a parfois une meilleure lisibilité et peut mener à une optimisation différente dans les plans d'exécution.

Toutes les sous-requêtes imbriquées sont-elles optimisées en jointures analogues ?

Non. Tous les SGBD ne peuvent pas faire cela de manière uniforme, parfois une sous-requête imbriquée entraîne un scan de chaque ligne, surtout s'il y a une corrélation entre la requête externe et interne.

Erreurs typiques et anti-modèles

Utilisation de sous-requêtes corrélées imbriquées dans SELECT avec de grands volumes de données.
Duplication des conditions de filtrage dans la requête interne et externe.
Tentative de remplacer toutes les sous-requêtes imbriquées par JOIN sans analyser les tâches.

Exemple de la vie réelle

Cas négatif

Un responsable des ventes a créé un rapport sur les clients en comptant le nombre de commandes dans une sous-requête interne. Les temps d'exécution étaient de plusieurs minutes, et la charge sur le serveur augmentait de manière exponentielle.

Avantages :

Logique claire Inconvénients :
Rapport très lent sur un grand nombre de clients
Charge serveur inefficace

Cas positif

La requête a été réécrite en utilisant LEFT JOIN et regroupement.