Niveau 15 : EXISTS et sous-requêtes corrélées

Leçon

Les sous-requêtes du niveau 14 sont indépendantes : on peut les exécuter seules. Une sous-requête corrélée, elle, utilise une colonne de la requête principale. Elle ne peut pas s'exécuter seule : elle est recalculée pour chaque ligne de la requête principale.

Exemple : afficher les employés qui gagnent plus que la moyenne de leur service. La moyenne à comparer change selon l'employé. On écrit FROM staff s WHERE salary > (SELECT AVG(salary) FROM staff WHERE department = s.department). Pour Bob (IT), la sous-requête calcule la moyenne du service IT ; pour Claire, celle de Finance. Le lien se fait par s.department, qui vient de la requête principale.

Quand la même table apparaît dans les deux requêtes, l'alias est indispensable : s désigne la ligne en cours de la requête principale. Sans lui, department = department comparerait la colonne à elle-même, et la sous-requête calculerait la moyenne de toute l'entreprise.

EXISTS (sous-requête) est vrai si la sous-requête renvoie au moins une ligne, faux sinon. Il répond à la question « y a-t-il au moins un… ? ». WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) garde les clients qui ont au moins une commande. On écrit SELECT 1, car seule compte l'existence d'une ligne, pas son contenu.

NOT EXISTS est vrai quand la sous-requête ne renvoie rien : les clients sans commande. C'est la méthode la plus sûre pour trouver ce qui n'a pas de correspondance, car EXISTS ne répond que vrai ou faux et n'est pas piégé par les NULL, contrairement à NOT IN.

Le lien entre les deux requêtes se fait dans le WHERE de la sous-requête. L'oublier rend EXISTS vrai pour toutes les lignes dès que la table interrogée n'est pas vide.

Syntaxe

SELECT …
FROM a
WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id);

Exemple commenté

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Pour chaque client, la sous-requête cherche au moins une commande à son nom. Fabio, qui n'a rien commandé, est écarté.

Résultat de l’exemple

À retenir

• Corrélée = la sous-requête dépend de la ligne en cours.
• EXISTS : au moins une ligne ; NOT EXISTS : aucune.
• SELECT 1 suffit dans un EXISTS.

Pièges fréquents

• Oublier la condition de liaison rend EXISTS vrai pour toutes les lignes.
• Donne des alias différents aux tables de la requête et de la sous-requête quand c'est la même table.

Les 5 exercices du niveau

1. Guidé · Affiche le nom des réalisateurs qui ont au moins un film dans la table movies. (Tables : directors, movies)
2. Entraînement · Affiche le nom des clients de l'hôtel qui n'ont jamais fait de réservation. (Tables : guests, bookings)
3. Entraînement · Affiche le nom, le service et le salaire des employés qui gagnent plus que la moyenne de leur propre service. (Table : staff)
4. Entraînement · Affiche le nom des clients qui ont au moins une commande annulée (status = 'cancelled'). (Tables : customers, orders)
5. Défi · Pour chaque genre, affiche le genre, le titre et la note du film le mieux noté. (Table : movies)

Dans SpeedQL, chaque requête est corrigée tout de suite : le résultat est comparé à celui attendu, puis la requête est relancée sur une base de contrôle cachée. Chaque exercice a ses indices écrits, à afficher seulement si tu bloques.

Voir aussi : la fiche de l’aide-mémoire · les exercices SQL corrigés sur cette notion