Sous-requêtes
Utiliser le résultat d'une requête comme condition d'une autre : c'est le principe de la sous-requête. Plutôt que de chercher une valeur à la main pour la coller dans un WHERE, on la calcule directement à l'intérieur de la requête.
L'arsenal à sa puissance maximale
Jusqu'ici, les valeurs de filtrage étaient connues à l'avance — une note seuil, une année, un genre. Mais certaines questions ne se posent pas ainsi : « quel film a la note la plus haute ? » ou « quels films sont réalisés par un Américain ? ». Ces valeurs doivent être calculées avant de pouvoir filtrer.
L'instinct serait de lancer une première requête pour trouver la valeur, puis d'écrire une deuxième requête avec ce chiffre en dur. Mais si on ajoute demain un film mieux noté ou un nouveau réalisateur, la deuxième requête devient fausse. La sous-requête résout cela en imbriquant la première requête à l'intérieur de la seconde.
Table films (fictive) — les colonnes utiles à cette leçon
| titre | note | realisateur_id |
|---|---|---|
| Inception | 8.8 | 1 |
| Le Parrain | 9.2 | 2 |
| Pulp Fiction | 8.9 | 3 |
| Amélie Poulain | 8.3 | 4 |
Le Parrain affiche 9.2 — la note la plus haute du catalogue. La colonne realisateur_id vaut 3 pour Pulp Fiction, mais rien dans cette table n'indique que 3 = Quentin Tarantino (c'est la table realisateurs qui fait correspondre les ids aux noms). Ces deux situations — calculer la meilleure note, retrouver un nom via son id — appellent la même réponse : la sous-requête.
Sous-requête — une requête dans une requête
Une sous-requête s'écrit entre parenthèses, directement dans la condition WHERE. PostgreSQL l'évalue en premier, obtient une valeur, puis l'utilise dans la requête principale. On peut employer n'importe quel comparateur — =, >, <, <=, >= — du moment que la sous-requête renvoie une seule valeur (on parle de sous-requête scalaire).
SELECT titre, note
FROM films
WHERE note = (SELECT MAX(note) FROM films)| titre | note |
|---|---|
| Le Parrain | 9.2 |
(SELECT MAX(note) FROM films) est exécutée en premier et renvoie 9.2. La requête externe reçoit alors WHERE note = 9.2 et isole Le Parrain. Si un cinquième film venait surpasser cette note demain, la requête s'adapterait automatiquement — aucun chiffre en dur.
Sous-requête scalaire — n'importe quelle table
La sous-requête peut interroger une table différente de la requête principale. La table realisateurs (rencontrée en jointure) stocke l'id, le nom et la nationalite de chaque réalisateur. films ne conserve que realisateur_id — un entier seul, sans le nom.
Pour retrouver les films de Quentin Tarantino sans connaître son id par cœur, on laisse la sous-requête le chercher à notre place :
SELECT titre, annee
FROM films
WHERE realisateur_id = (SELECT id FROM realisateurs WHERE nom = 'Quentin Tarantino')| titre | annee |
|---|---|
| Pulp Fiction | 1994 |
(SELECT id FROM realisateurs WHERE nom = 'Quentin Tarantino') renvoie 3 — son identifiant dans realisateurs. La requête principale filtre ensuite films sur realisateur_id = 3. On n'a jamais eu besoin de savoir que l'id était 3 : la sous-requête le découvre à chaque exécution, quelle que soit l'évolution du catalogue.
Sous-requête liste — plusieurs valeurs avec IN
La section précédente filtrait sur un réalisateur précis — la sous-requête renvoyait un seul id. Filtrer sur une nationalité entière renvoie plusieurs ids (Coppola et Tarantino sont tous deux Américains). Le comparateur = ne suffit plus : PostgreSQL refuserait une comparaison entre une valeur et une liste.
IN prend le relais : il teste si la valeur de gauche appartient à la liste renvoyée par la sous-requête.
SELECT titre, note
FROM films
WHERE realisateur_id IN (SELECT id FROM realisateurs WHERE nationalite = 'Américain')
ORDER BY note DESC| titre | note |
|---|---|
| Le Parrain | 9.2 |
| Pulp Fiction | 8.9 |
La sous-requête renvoie deux ids : 2 (Francis Ford Coppola) et 3 (Quentin Tarantino). IN vérifie l'appartenance pour chaque film — Le Parrain (realisateur_id = 2) et Pulp Fiction (3) passent ; Inception (1, Britannique) et Amélie Poulain (4, Français) sont exclus. Avec = à la place d'IN, PostgreSQL aurait renvoyé une erreur dès que la sous-requête retourne plus d'une ligne. Règle simple : dès que le sous-select peut renvoyer plusieurs valeurs, remplacer = par IN.
Tout combiner
Les sous-requêtes se combinent avec tout l'arsenal des leçons précédentes : JOIN, GROUP BY, ORDER BY. La question suivante les mobilise en même temps : par nationalité de réalisateur, combien de films ont une note supérieure ou égale à la moyenne du catalogue ?
SELECT r.nationalite, COUNT(*) AS nb_films_sup_moy
FROM films f
INNER JOIN realisateurs r ON f.realisateur_id = r.id
WHERE f.note >= (SELECT AVG(note) FROM films)
GROUP BY r.nationalite
ORDER BY nb_films_sup_moy DESC| nationalite | nb_films_sup_moy |
|---|---|
| Américain | 2 |
| Britannique | 1 |
La sous-requête calcule AVG(note) = 8.8 une fois pour toutes. WHERE f.note >= 8.8 inclut trois films : Le Parrain (9.2), Pulp Fiction (8.9) et Inception (8.8 — l'égalité compte avec >=). INNER JOIN rattache chaque film à son réalisateur ; Fritz Lang (sans film dans le catalogue) est éliminé avant le regroupement, Jeunet/Amélie Poulain (8.3) est écarté par le WHERE. GROUP BY répartit les trois films restants par nationalité : Américain (Le Parrain + Pulp Fiction) et Britannique (Inception). Changer >= en > modifierait le résultat : 8.8 > 8.8 est faux, Inception serait exclu, la catégorie Britannique disparaîtrait — il ne resterait qu'une seule ligne (Américain : 2 films). Le choix de l'opérateur sur une valeur de bord n'est jamais anodin : >= inclut les égaux, > les écarte.
Récapitulatif
Ce que tu retiens de cette leçon :
SELECT r.nationalite, COUNT(*) AS nb_films_sup_moyFROM films fINNER JOIN realisateurs r ON f.realisateur_id = r.idWHERE f.note >= (SELECT AVG(note) FROM films)GROUP BY r.nationaliteORDER BY nb_films_sup_moy DESC💡 Bon à savoir : le parcours se termine par une enquête. Pas de clause nommée dans l'énoncé — juste une question métier. À toi de choisir les bons outils.
Passe à la pratique
Cette leçon t'a montré la théorie. Pour maîtriser SQL, rien ne vaut la pratique : accède aux exercices et au bac à sable PostgreSQL réel.