Les sous-requêtes corrélées en SQL

Leçon 6.3 · Temps de lecture : ~6 min · Exercices pratiques ↓

Une sous-requête corrélée SQL est une sous-requête qui fait référence à une colonne de la requête externe ; son résultat dépend donc de la ligne externe courante. Dans cette leçon, vous apprendrez comment une sous-requête corrélée est évaluée, comment comparer une ligne avec son propre groupe, comment utiliser des sous-requêtes corrélées dans SELECT et avec EXISTS, et quand un JOIN est le meilleur choix.

Les sous-requêtes des leçons précédentes étaient indépendantes : on pouvait sélectionner la requête interne et l'exécuter seule. Une sous-requête corrélée ne peut pas s'exécuter seule, car elle utilise une valeur de la ligne que la requête externe examine à ce moment-là. Cela permet de poser des questions relatives à chaque ligne : « ce film est-il plus cher que la moyenne de sa classification ? », « quel est le film le plus long de cette catégorie ? ».

Sous-requête corrélée SQL : pour chaque ligne de la requête externe, la requête interne s'exécute avec la valeur de cette ligne et son résultat est comparé à la ligne

Comment fonctionne une sous-requête corrélée

Logiquement, la base de données évalue une sous-requête corrélée ainsi :

Elle prend une ligne de la requête externe.
Elle exécute la requête interne en y substituant les valeurs de cette ligne.
Elle utilise le résultat pour évaluer la condition ou la colonne de la ligne.
Elle passe à la ligne suivante et recommence.

Le lien entre les requêtes est une condition de la requête interne qui utilise un alias externe, par exemple f2.rating = f1.rating. Les alias sont indispensables : sans eux, on ne sait pas à quelle copie de la table appartient une colonne.

Comparer une ligne avec son groupe

Les films dont le coût de remplacement est supérieur à la moyenne de leur classification :

SELECT f1.title, f1.rating, f1.replacement_cost
FROM film AS f1
WHERE f1.replacement_cost > (
    SELECT AVG(f2.replacement_cost)
    FROM film AS f2
    WHERE f2.rating = f1.rating
)
ORDER BY f1.rating, f1.replacement_cost DESC;

Résultat : les films dont le remplacement coûte plus cher que le film moyen de la même classification.

Pour un film classé PG, la requête interne calcule la moyenne des films PG ; pour un film R, celle des films R. Une sous-requête non corrélée ne pourrait comparer qu'avec une seule moyenne globale.

Les sous-requêtes corrélées dans SELECT

Une sous-requête corrélée dans la liste SELECT renvoie une valeur pour chaque ligne. Le nombre de films de chaque catégorie :

SELECT c.name AS category,
       (SELECT COUNT(*)
        FROM film_category AS fc
        WHERE fc.category_id = c.category_id) AS films
FROM category AS c
ORDER BY films DESC;

Résultat : 16 catégories avec leur nombre de films, en commençant par Sports (74 films).

Elle peut aussi renvoyer une valeur difficile à obtenir avec GROUP BY, comme le titre du film le plus long de chaque catégorie :

SELECT c.name AS category,
       (SELECT f.title
        FROM film AS f
        INNER JOIN film_category AS fc ON fc.film_id = f.film_id
        WHERE fc.category_id = c.category_id
        ORDER BY f.length DESC, f.title
        LIMIT 1) AS longest_film
FROM category AS c
ORDER BY c.name;

Résultat : chaque catégorie avec le titre de son film le plus long ; les égalités sont départagées par le titre.

Une sous-requête dans SELECT doit renvoyer une ligne et une colonne ; sinon, la requête échoue.

Les sous-requêtes corrélées avec EXISTS

EXISTS vérifie si la requête interne renvoie au moins une ligne, et elle est presque toujours corrélée. Les clients qui ont loué au moins un exemplaire du magasin 2 :

SELECT c.customer_id, c.first_name, c.last_name
FROM customer AS c
WHERE EXISTS (
    SELECT 1
    FROM rental AS r
    INNER JOIN inventory AS i ON i.inventory_id = r.inventory_id
    WHERE r.customer_id = c.customer_id
      AND i.store_id = 2
)
ORDER BY c.customer_id;

Résultat : les clients ayant au moins une location d'un exemplaire du magasin 2. SELECT 1 est une convention : EXISTS vérifie seulement qu'une ligne existe, pas ce qu'elle contient.

NOT EXISTS trouve les lignes sans correspondance, comme l'anti-jointure LEFT JOIN ... IS NULL de la leçon 5.3.

Performance

La description « exécutée pour chaque ligne » est le modèle logique. Les optimiseurs modernes réécrivent souvent les sous-requêtes corrélées en jointures ou semi-jointures, en particulier EXISTS et IN. Néanmoins :

Une sous-requête corrélée dans SELECT ou dans un WHERE complexe peut réellement s'exécuter une fois par ligne externe et devenir lente sur de grandes tables.
Un index sur la colonne de corrélation (ici film_category.category_id, rental.customer_id) rend chaque exécution interne rapide.
Si un même résultat interne est nécessaire pour de nombreuses colonnes, un JOIN avec une sous-requête groupée ou une CTE (leçon 6.4) est généralement plus clair et plus rapide.

Si une requête avec une sous-requête corrélée est lente, vérifiez le plan avec EXPLAIN.

Points clés :

Une sous-requête corrélée fait référence à une colonne de la requête externe et ne peut pas s'exécuter seule.
Logiquement, elle est évaluée une fois pour chaque ligne externe.
C'est l'outil naturel pour comparer une ligne avec son propre groupe.
Dans SELECT, elle doit renvoyer exactement une valeur.
EXISTS et NOT EXISTS sont des sous-requêtes corrélées typiques.
Indexez la colonne de corrélation ; envisagez un JOIN ou une CTE pour les requêtes lourdes.

Questions fréquentes

Quelle est la différence entre une sous-requête corrélée et une sous-requête non corrélée ?

Une sous-requête non corrélée est indépendante : elle s'exécute une fois et son résultat sert pour toutes les lignes. Une sous-requête corrélée utilise des valeurs de la ligne externe, son résultat peut donc différer pour chaque ligne.

Les sous-requêtes corrélées sont-elles lentes ?

Elles peuvent l'être, car logiquement elles s'exécutent pour chaque ligne externe. En pratique, les optimiseurs les transforment souvent en jointures, et un index sur la colonne de corrélation aide beaucoup. Vérifiez le plan d'exécution avec EXPLAIN.

Peut-on remplacer une sous-requête corrélée par un JOIN ?

Généralement oui. Par exemple, la comparaison avec la moyenne du groupe peut s'écrire comme une jointure avec une sous-requête groupée : JOIN (SELECT rating, AVG(replacement_cost) AS avg_cost FROM film GROUP BY rating) AS r ON r.rating = f.rating.