Niveau 10 : LEFT JOIN et jointures multiples

Leçon

JOIN fait disparaître les lignes sans correspondance. Or ce sont souvent elles qui comptent : les clients qui n'ont jamais commandé, les livres jamais empruntés. LEFT JOIN les garde.

LEFT JOIN garde toutes les lignes de la table de gauche, celle écrite dans le FROM. Quand une ligne a une correspondance, on obtient la même chose qu'avec JOIN. Quand elle n'en a pas, elle est quand même gardée, et toutes les colonnes de la table de droite valent NULL. FROM directors d LEFT JOIN movies m … garde ainsi Sara Diaz, avec un titre NULL.

Pour trouver les « orphelins », on fait un LEFT JOIN puis WHERE droite.id IS NULL : seules restent les lignes de gauche sans correspondance. Pour compter, utilise COUNT(droite.id), qui vaut 0 pour une ligne sans correspondance ; COUNT(*) compterait 1, car la ligne existe bien dans le résultat.

Piège classique : une condition sur la table de droite placée dans le WHERE. Le WHERE est exécuté après la jointure ; pour une ligne sans correspondance, la colonne de droite vaut NULL, le test échoue et la ligne disparaît, comme avec un JOIN ordinaire. Pour filtrer la table de droite sans perdre les lignes de gauche, mets la condition dans le ON : LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'. Le défi 10.5 te fait rencontrer ce piège.

On peut enchaîner autant de jointures que nécessaire, chacune avec son ON. Une table peut aussi être jointe à elle-même : c'est une auto-jointure. Dans staff, manager_id pointe vers l'id d'un autre employé. FROM staff e LEFT JOIN staff m ON m.id = e.manager_id lit la table deux fois sous deux alias : e pour l'employé, m pour son manager. m.name donne alors le nom du manager, et le LEFT JOIN garde Alice, qui n'en a pas.

Syntaxe

SELECT …
FROM a
LEFT JOIN b ON b.a_id = a.id
JOIN c ON c.id = b.c_id;

Exemple commenté

SELECT d.name, m.title
FROM directors d
LEFT JOIN movies m ON m.director_id = d.id;

Tous les réalisateurs sont listés. Sara Diaz, qui n'a aucun film, apparaît avec un titre vide (NULL).

Résultat de l’exemple

À retenir

• LEFT JOIN garde toute la table de gauche.
• LEFT JOIN + IS NULL trouve les « orphelins ».
• Une auto-jointure utilise deux alias pour la même table.

Pièges fréquents

• Un WHERE sur une colonne de la table de droite peut annuler l'effet du LEFT JOIN.
• COUNT(*) compte 1 même quand il n'y a aucune correspondance : utilise COUNT(colonne_de_droite).

Pour aller plus loin

RIGHT JOIN garde toute la table de droite et FULL JOIN garde les deux côtés (disponibles dans SQLite depuis la version 3.39). CROSS JOIN associe chaque ligne à toutes les lignes de l'autre table : c'est le produit cartésien, utile pour générer toutes les combinaisons.

Les 5 exercices du niveau

1. Guidé · Affiche le nom de chaque artiste et son nombre de chansons, y compris les artistes qui n'en ont aucune (0). (Tables : artists, songs)
2. Entraînement · Affiche le nom des membres qui n'ont jamais emprunté de livre. (Tables : members, loans)
3. Entraînement · Pour chaque prêt, affiche le nom du membre, le titre du livre et la date de prêt. (Tables : loans, members, books)
4. Entraînement · Affiche le nom de chaque employé et le nom de son manager (vide pour la personne qui n'en a pas). (Table : staff)
5. Défi · Pour chaque client, affiche son nom et le nombre de ses commandes non expédiées, c'est-à-dire dont le statut est différent de 'shipped' (not_shipped). Les clients qui n'en ont aucune doivent apparaître avec 0. (Tables : customers, orders)

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