Jointures simples
Exploitation d'une base de données MariaDB/MySQL
Petite vérification
Avant de commencer, vérifiez si vous savez afficher la liste des tables de la base bibliotheque, en ligne de commandes, et vérifier que vous obtenez bien 5 tables
📌 Réponse attendue
Notions théoriques
Jusqu'à présent, vos requêtes SQL n'interrogeaient qu'une seule table à la fois. Or, dans une base de données bien conçue (voir le MLD ci-dessous), les informations sont réparties dans plusieurs tables reliées entre elles par des clés étrangères.
MLD de la base bibliotheque
- AUTEURS (idauteur, nom, prenom, datenaiss, datedeces, bibliographie)
- EDITEURS (idediteur, nom, adresse, code, ville, pays, telephone, fax)
- EMPRUNTEURS (idemprunteur, nom, prenom, adresse, code, ville, telephone, sexe, datenaiss, nbretards)
- EMPRUNTS (idemprunt, datepret, daterendu, #idlivre, #idemprunteur)
- LIVRES (idlivre, isbn, titre, nbpages, dateparu, prix, theme, format, #idediteur, #idauteur)
Par exemple, la table livres ne contient pas le nom de l'auteur : elle contient seulement la colonne idauteur, qui est une clé étrangère pointant vers la ligne correspondante de la table auteurs. Pour afficher le titre d'un livre et le nom de son auteur dans le même résultat, il faut donc joindre ces deux tables.
Une jointure permet de combiner les lignes de deux (ou plusieurs) tables, en se basant sur une colonne commune : la clé étrangère d'une table qui correspond à la clé primaire d'une autre.
La jointure avec WHERE
La méthode la plus simple pour écrire une jointure consiste à lister les tables dans la clause FROM (séparées par une virgule), puis à préciser la condition de correspondance dans la clause WHERE :
SELECT livres.titre, auteurs.nom
FROM livres, auteurs
WHERE livres.idauteur = auteurs.idauteur;
Si vous oubliez la condition WHERE livres.idauteur = auteurs.idauteur, MySQL/MariaDB combine chaque livre avec chaque auteur, sans lien logique entre eux : c'est ce qu'on appelle un produit cartésien. Avec 13 livres et 6 auteurs, cela donnerait 13 × 6 = 78 lignes, la plupart n'ayant aucun sens !
Remarquez qu'on écrit livres.idauteur et non simplement idauteur : dès qu'on interroge plusieurs tables, il faut préfixer le nom de la colonne par le nom de sa table (table.colonne), notamment lorsque deux tables partagent le même nom de colonne (par exemple nom, présent à la fois dans auteurs, editeurs et emprunteurs).
La jointure avec JOIN
La syntaxe JOIN ... ON ... est la façon moderne et recommandée d'écrire une jointure. Elle sépare clairement la condition de jointure (ON) d'une éventuelle condition de filtrage supplémentaire (WHERE) :
SELECT livres.titre, auteurs.nom
FROM livres
JOIN auteurs ON livres.idauteur = auteurs.idauteur;
Cette requête produit exactement le même résultat que la version avec WHERE ci-dessus. On peut ensuite ajouter une clause WHERE normalement, pour filtrer le résultat de la jointure :
SELECT livres.titre, auteurs.nom
FROM livres
JOIN auteurs ON livres.idauteur = auteurs.idauteur
WHERE auteurs.nom = 'Zola';
La syntaxe JOIN ... ON ... est préférée à la jointure avec WHERE, car elle distingue clairement :
- la condition de jointure (comment les tables sont reliées) → dans le
ON - la condition de filtrage (quelles lignes on garde) → dans le
WHERE
Cela rend les requêtes plus lisibles, même sur une jointure simple entre deux tables.
Exemple pratique
-- Afficher le prénom et nom de chaque emprunteur, avec la date de prêt de chaque emprunt qu'il a effectué
SELECT emprunteurs.prenom, emprunteurs.nom, emprunts.datepret
FROM emprunts
JOIN emprunteurs ON emprunts.idemprunteur = emprunteurs.idemprunteur
ORDER BY emprunts.datepret;
On peut ajouter ORDER BY après une jointure exactement comme sur une requête à une seule table : il s'applique sur le résultat final, une fois les tables combinées.
Test de mémorisation/compréhension
TP pour réfléchir et résoudre des problèmes
La documentaliste souhaite maintenant obtenir des informations qui nécessitent de croiser plusieurs tables de la base bibliotheque. Vous allez répondre à ses questions, d'abord avec une jointure WHERE, puis avec une jointure JOIN.
Étape 1 — Une première jointure avec WHERE
Ici, tous les livres ont un auteur renseigné : vous devez donc obtenir 13 lignes, soit autant que de livres dans la table livres. Si vous obtenez plus ou moins de lignes, c'est le signe d'une erreur dans la condition de jointure.
Étape 2 — La même idée, avec JOIN
Chaque jointure ne relie que deux tables à la fois, via une seule clé étrangère. Vous devez obtenir 13 lignes, car tous les livres ont un éditeur renseigné : c'est le même résultat qu'avec la jointure WHERE de l'étape 1, mais écrit avec JOIN.
Étape 3 — Une jointure JOIN avec un filtre WHERE
JOIN ... ON ... sert uniquement à relier les tables. Une fois les tables jointes, on peut toujours ajouter une clause WHERE pour filtrer le résultat, exactement comme sur une seule table. Ici, vous devez obtenir 3 lignes : Germinal, Thérèse Raquin et Au Bonheur des Dames.
Étape 4 — Une jointure JOIN filtrée sur l'éditeur
Vous devez obtenir 3 lignes : Germinal, Le Cid et Voyage au centre de la Terre. Si le nombre de lignes ne correspond pas, relisez votre condition ON avant de chercher ailleurs.
Étape 5 — Jointure entre emprunts et emprunteurs
Rien n'oblige à partir de la table livres ou auteurs : on peut tout aussi bien joindre emprunts avec emprunteurs, tant que la condition ON relie la bonne clé étrangère à la bonne clé primaire. Vous devez obtenir 5 lignes, soit autant que d'emprunts enregistrés.
Étape 6 — Jointure entre emprunts et livres, triée
ORDER BY s'applique toujours en dernier, une fois le résultat de la jointure (et du WHERE éventuel) déjà calculé. Le premier livre affiché doit être Rhinocéros (emprunté le 2019-09-15), le dernier Le Petit Prince (emprunté le 2019-12-17).