SQL : jointures, agrégation et requêtes imbriquées
Cours · informatique (tronc commun des prépas scientifiques), chapitre 16 · prépas scientifiques, tronc commun · le début du chapitre, la suite sur Adloun
16.1 Introduction et motivation
Le chapitre 15 a séparé pour bien ranger : les auteurs d'un côté, les livres de l'autre, les emprunts dans leur table d'association — chaque fait en un seul endroit. Mais les questions, elles, ignorent les frontières : « qui a emprunté Guerre et paix ? » traverse trois tables. Il faut donc l'opération inverse de la séparation : la jointure, qui recolle les tables le long de leurs clés — née au chapitre précédent du produit cartésien filtré, elle reçoit ici sa syntaxe officielle et ses usages, jusqu'à l'autojointure d'une table avec elle-même.
Deuxième puissance de SQL : compter, sommer, moyenner — l'agrégation. Une médiathèque ne veut pas la liste des emprunts, elle veut le palmarès des livres, le total par usager, la moyenne par auteur : les fonctions COUNT, SUM, AVG, MIN, MAX, démultipliées par GROUP BY qui calcule par groupe, et filtrées par HAVING — le WHERE des groupes. Avec les requêtes imbriquées en clef de voûte, ce chapitre complète la panoplie SQL du programme : de quoi poser à une base à peu près n'importe quelle question raisonnable — en trois lignes déclaratives.
16.2 La jointure interne
16.2.1 Recoller ce que la modélisation a séparé
La jointure interne de deux tables s'écrit
SELECT ... FROM t1 JOIN t2 ON conditionet produit toutes les combinaisons d'une ligne de t1 et d'une ligne de t2 qui satisfont la condition — exactement le produit cartésien filtré de l'exercice 8 du chapitre 15, en mieux dit. Conformément au programme, la condition est une équi-jointure : une conjonction d'égalités, presque toujours « clé étrangère clé primaire » :
SELECT titre, nom, annee
FROM livre JOIN auteur ON livre.id_auteur = auteur.id_auteur
WHERE naissance = 1828
ORDER BY annee;
De la Terre à la Lune | Verne | 1865
Guerre et paix | Tolstoï | 1869
Vingt mille lieues sous les mers | Verne | 1870
Michel Strogoff | Verne | 1876
Anna Karénine | Tolstoï | 1877Les cinq livres des deux auteurs nés en . La condition du ON apparie chaque livre à son auteur ; le WHERE peut alors mêler librement des attributs des deux tables — la ligne jointe les porte tous.
Méthode : Vérifier une sortie de jointure à la main
Trois contrôles mécaniques, ligne par ligne, comme on déroulait les boucles du chapitre 1 : (1) chaque ligne du résultat satisfait-elle le ON (le bon auteur sur le bon livre) ? (2) le WHERE (ici : Orgueil et préjugés, Austen , doit être absent) ? (3) l'ORDER BY ( en tête) ? Et un quatrième, global : le compte — une jointure clé étrangère clé primaire rend exactement une ligne par ligne de la table côté qui passe les filtres (chapitre 15, exercice 8). Tout résultat de jointure qui surprend par sa taille — surtout en trop — trahit presque toujours une condition de ON incomplète : le fantôme du produit cartésien.
« Qui a emprunté quoi, et quand ? » traverse usager, emprunt et livre — on joint de proche en proche, en suivant les flèches de clés étrangères du schéma :
SELECT usager.nom, livre.titre, emprunt.jour
FROM emprunt
JOIN usager ON emprunt.id_usager = usager.id_usager
JOIN livre ON emprunt.id_livre = livre.id_livre
WHERE jour >= '2025-10-01'
ORDER BY jour;
Chloé | Le Blé en herbe | 2025-10-01
Chloé | Anna Karénine | 2025-10-02
Bruno | Michel Strogoff | 2025-10-05
Emma | Guerre et paix | 2025-10-06
Alice | Orgueil et préjugés | 2025-10-08La table d'association est le pivot : chaque ligne d'emprunt attrape son usager d'un côté, son livre de l'autre. Les préfixes usager.nom, livre.titre lèvent les ambiguïtés ; on peut abréger avec des alias de table : FROM emprunt AS e JOIN usager AS u ON e.id_usager = u.id_usager.