Leçon 6.3 : Sous-requêtes corrélées

Dans les leçons précédentes, nous avons utilisé des sous-requêtes "autonomes" qui pouvaient être exécutées seules. Dans cette leçon, nous introduisons les sous-requêtes corrélées — un type avancé de sous-requête qui dépend des valeurs de la requête principale.

Qu'est-ce qu'une sous-requête corrélée ?

Une sous-requête est corrélée lorsqu'elle fait référence à une colonne d'une table de la requête principale. Contrairement à une sous-requête classique, une sous-requête corrélée ne peut pas être exécutée indépendamment de la requête principale.

Comment ça fonctionne :

1. La base de données prend une ligne de la requête principale.
2. Elle exécute la requête interne en utilisant les valeurs de cette ligne spécifique.
3. Elle utilise le résultat de la requête interne pour satisfaire la clause WHERE (ou SELECT).
4. Elle passe à la ligne suivante et répète le processus.

Note sur la performance : Une sous-requête corrélée peut être exécutée une fois pour chaque ligne de la requête principale, ce qui peut être plus lent qu'un JOIN ou une sous-requête classique sur de grands ensembles de données.

1. Sous-requêtes corrélées dans WHERE

L'usage le plus courant est de comparer la valeur d'une ligne à un ensemble de données lié spécifiquement à cette ligne.

Scénario : Trouver tous les films dont le coût de remplacement est supérieur à la moyenne des films de la même catégorie de classification (ex. G, PG, R).

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

• Corrélation : f1.rating = f2.rating lie la requête interne à la ligne courante de la requête principale.
• Logique : Pour chaque film, la base calcule la moyenne du coût de remplacement pour sa classification et vérifie si ce film coûte plus cher.

2. Sous-requêtes corrélées dans SELECT

Vous pouvez utiliser des sous-requêtes corrélées pour récupérer des données descriptives ou des agrégats pour chaque ligne sans utiliser la clause GROUP BY.

Scénario : Afficher la liste des catégories et le titre du film le plus long dans chaque catégorie.

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

3. Sous-requêtes corrélées avec EXISTS

Nous avons vu l'opérateur EXISTS dans la leçon précédente. EXISTS est presque toujours utilisé avec une sous-requête corrélée.

Scénario : Trouver les clients ayant loué au moins un film dans un magasin spécifique (Magasin 1).

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

Points clés de cette leçon

• Une sous-requête corrélée dépend de la requête principale pour ses valeurs.
• Elle est exécutée ligne par ligne (une fois pour chaque ligne candidate).
• Les alias sont essentiels pour distinguer les instances de table interne et externe.
• Elles sont puissantes pour les comparaisons relatives à un groupe (comparer une ligne à son propre groupe).
• Attention à la performance sur des millions d'enregistrements.