Niveau 14 : Requêtes dans les requêtes : sous-requêtes

Leçon

Parfois, une question en contient une autre. « Quels films ont une note supérieure à la moyenne ? » demande d'abord de calculer la moyenne, puis de comparer chaque film à elle. Une sous-requête répond à la première question à l'intérieur de la seconde : c'est un SELECT complet écrit entre parenthèses dans une autre requête.

Pourquoi ne pas écrire WHERE rating > AVG(rating) ? Parce que WHERE travaille ligne par ligne, avant tout calcul d'agrégat. La sous-requête (SELECT AVG(rating) FROM movies) est une requête indépendante, calculée à part : sa valeur (7.2) est ensuite utilisée comme un simple nombre.

Une sous-requête scalaire renvoie une seule valeur, une ligne et une colonne : on la compare avec =, < ou >. Elle doit vraiment renvoyer une seule ligne : en SQL standard, plusieurs lignes provoquent une erreur, et SQLite prend silencieusement la première.

Une sous-requête qui renvoie une colonne de plusieurs valeurs s'utilise avec IN : WHERE author_id IN (SELECT id FROM authors WHERE country = 'Sweden'). NOT IN demande de la prudence : si la sous-requête renvoie un seul NULL, NOT IN ne renvoie plus aucune ligne (voir le niveau 12). Ajoute WHERE … IS NOT NULL dans la sous-requête, ou utilise NOT EXISTS (niveau 15).

Une sous-requête peut enfin remplacer une table dans FROM : on interroge alors son résultat comme une table temporaire, à laquelle il faut donner un alias. FROM (SELECT seller, SUM(amount) AS total FROM sales GROUP BY seller) AS t permet par exemple de calculer la moyenne des totaux par vendeur.

Conseil de lecture : commence toujours par la sous-requête. Exécute-la seule pour voir ce qu'elle renvoie, puis lis la requête principale.

Syntaxe

SELECT …
FROM t
WHERE x > (SELECT AVG(x) FROM t) AND id IN (SELECT t_id FROM u);

Exemple commenté

SELECT title, rating
FROM movies
WHERE rating > (SELECT AVG(rating) FROM movies);

La sous-requête calcule la note moyenne (7.2) ; la requête principale garde les films qui la dépassent.

Résultat de l’exemple

À retenir

• La sous-requête s'écrit entre parenthèses.
• Scalaire : une valeur, comparée avec = < >. Colonne : utilisée avec IN.
• Dans FROM, une sous-requête se comporte comme une table temporaire.

Pièges fréquents

• WHERE salary > AVG(salary) est interdit : il faut une sous-requête.
• NOT IN avec une sous-requête qui contient un NULL ne renvoie rien.

Pour aller plus loin

Le SQL standard propose aussi x > ALL (sous-requête) (« plus grand que toutes les valeurs ») et x > ANY (sous-requête) (« plus grand qu'au moins une »). SQLite ne les connaît pas : on écrit x > (SELECT MAX(…) …) et x > (SELECT MIN(…) …). Un détail diffère : sur une sous-requête vide, > ALL est vrai, alors que la comparaison au maximum donne NULL.

IN ou EXISTS ? Pour garder les lignes qui ont une correspondance, les deux donnent le même résultat. Pour l'inverse, préfère NOT EXISTS à NOT IN dès que la colonne peut contenir NULL (exercice 14.4).

Les 5 exercices du niveau

1. Guidé · Affiche le nom et le salaire des employés qui gagnent plus que le salaire moyen. (Table : staff)
2. Entraînement · Affiche le titre des livres écrits par des auteurs suédois ou allemands. (Tables : books, authors)
3. Entraînement · Affiche l'identifiant, le type et le prix de la chambre la plus chère. (Table : rooms)
4. Entraînement · Affiche le nom des réalisateurs qui n'ont réalisé aucun film. Attention : un film de la table n'a pas de réalisateur connu. (Tables : directors, movies)
5. Défi · Quelle est la moyenne des totaux de ventes par vendeur ? (Calcule d'abord le total de chaque vendeur, puis la moyenne de ces totaux.) (Table : sales)

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