Jointures sur plusieurs tables
Exploitation d'une base de données MariaDB/MySQL
Petite vérification
Avant de commencer, vérifiez si vous savez afficher le titre des livres publiés par l'éditeur Gallimard.
Utilisez JOIN entre livres et editeurs, et vérifiez que vous obtenez bien 3 lignes.
📌 Réponse attendue
Notions théoriques
Vous savez maintenant joindre 2 tables. Mais rien n'empêche d'en joindre trois, ou plus, du moment qu'il existe une chaîne de clés étrangères qui les relie entre elles.
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)
Chaîner plusieurs JOIN
Pour joindre 3 tables, on ajoute simplement un deuxième JOIN ... ON ... à la suite du premier. Par exemple, pour afficher le titre de chaque livre, le nom de son auteur et le nom de son éditeur :
SELECT livres.titre, auteurs.nom, editeurs.nom
FROM livres
JOIN auteurs ON livres.idauteur = auteurs.idauteur
JOIN editeurs ON livres.idediteur = editeurs.idediteur;
Chaque JOIN ajoute une seule table à la fois. La condition ON d'un nouveau JOIN peut faire référence à n'importe quelle table déjà présente dans la requête (ici, livres), pas seulement à la toute première table du FROM.
On peut aussi chaîner les jointures en partant d'une table qui n'est pas livres. Par exemple, pour afficher chaque emprunt avec le titre du livre concerné et le nom de l'emprunteur :
SELECT emprunts.datepret, livres.titre, emprunteurs.nom
FROM emprunts
JOIN livres ON emprunts.idlivre = livres.idlivre
JOIN emprunteurs ON emprunts.idemprunteur = emprunteurs.idemprunteur;
Construisez une requête à plusieurs jointures étape par étape : écrivez et testez d'abord un seul JOIN, vérifiez le résultat, puis ajoutez le JOIN suivant. Il est très difficile de déboguer 3 jointures écrites d'un coup si le résultat est faux.
LEFT JOIN : garder les lignes sans correspondance
Avec un JOIN classique (aussi appelé INNER JOIN), une ligne n'apparaît dans le résultat que si elle trouve une correspondance dans l'autre table. Mais certains livres de la base bibliotheque n'ont jamais été empruntés : avec un JOIN classique, ces livres disparaîtraient complètement du résultat.
Le mot-clé LEFT JOIN résout ce problème : il conserve toutes les lignes de la table de gauche (celle citée juste avant LEFT JOIN), même si aucune ligne correspondante n'existe dans la table de droite. Les colonnes de la table de droite sont alors remplies avec NULL.
SELECT livres.titre, emprunts.datepret
FROM livres
LEFT JOIN emprunts ON livres.idlivre = emprunts.idlivre;
Ici, livres est la table de gauche (avant LEFT JOIN) : toutes ses lignes sont conservées. Pour un livre jamais emprunté, la colonne emprunts.datepret affichera NULL.
Pour retrouver les livres jamais empruntés, on combine LEFT JOIN avec une condition WHERE ... IS NULL sur une colonne de la table de droite :
SELECT livres.titre
FROM livres
LEFT JOIN emprunts ON livres.idlivre = emprunts.idlivre
WHERE emprunts.idlivre IS NULL;
Cette technique très courante s'appelle une anti-jointure.
Pour l'instant, on se limite à LEFT JOIN. Il existe aussi RIGHT JOIN (symétrique, table de droite conservée), mais ce n'est pas l'objet de cette séance.
Exemple pratique
-- Afficher le titre, l'auteur et l'éditeur de chaque livre écrit par 'Verne'
SELECT livres.titre, auteurs.nom AS auteur, editeurs.nom AS editeur
FROM livres
JOIN auteurs ON livres.idauteur = auteurs.idauteur
JOIN editeurs ON livres.idediteur = editeurs.idediteur
WHERE auteurs.nom = 'Verne';
AS auteur et AS editeur sont des alias de colonne : ils renomment la colonne affichée dans le résultat, ce qui est bien pratique quand deux tables jointes partagent le même nom de colonne (nom, ici pour auteurs et editeurs).
Test de mémorisation/compréhension
TP pour réfléchir et résoudre des problèmes
La documentaliste a maintenant besoin de croiser 3 tables à la fois, puis de repérer les livres qui ne sont jamais empruntés. Vous allez d'abord enchaîner plusieurs JOIN, puis découvrir LEFT JOIN.
Étape 1 — Chaîner deux JOIN
Tous les livres ont un auteur et un éditeur renseignés : vous devez obtenir 13 lignes, exactement comme avec une seule jointure. Joindre une table de plus ne fait jamais apparaître de nouvelles lignes, tant que chaque livre a bien une correspondance dans les deux tables.
Étape 2 — Trois tables, avec un filtre
Le WHERE s'applique toujours après que toutes les jointures ont été effectuées. Vous devez obtenir 1 seule ligne : 20000 lieues sous les mers, emprunté le 2019-10-22.
Étape 3 — Trois tables, triées
Ici, emprunts est la table "pivot" : elle contient les deux clés étrangères (idlivre et idemprunteur) qui permettent de rejoindre livres et emprunteurs. Vous devez obtenir 5 lignes, la première étant Duval / Rhinocéros (2019-09-15), la dernière Martin / Le Petit Prince (2019-12-17).
Étape 4 — Trois tables, filtrées sur l'emprunteur
Vous devez obtenir 2 lignes : Germinal (2019-09-27) puis Le Petit Prince (2019-12-17). Que vous écriviez JOIN emprunteurs avant ou après JOIN livres, le résultat est identique : seul l'ordre des ON doit rester cohérent avec les tables déjà citées.
Étape 5 — Découvrir LEFT JOIN
Vous devez obtenir 13 lignes (une par livre). Pour les 5 livres déjà empruntés, datepret affiche une date. Pour les 8 autres (jamais empruntés, comme Le Cid ou Horace), datepret affiche NULL. Avec un simple JOIN, ces 8 livres auraient totalement disparu du résultat.
Étape 6 — Une anti-jointure avec LEFT JOIN
En SQL, on ne peut jamais tester = NULL : il faut obligatoirement écrire IS NULL (ou IS NOT NULL). Vous devez obtenir 8 lignes : Le Cid, Thérèse Raquin, Au Bonheur des Dames, Voyage au centre de la Terre, Notre-Dame de Paris, Vol de nuit, Horace et La Cantatrice chauve.