Leçon 5.3 · Temps de lecture : ~7 min

Le SQL LEFT JOIN renvoie chaque ligne de la table de gauche et les lignes correspondantes de la table de droite ; en l'absence de correspondance, les colonnes de la table de droite valent NULL. Dans cette leçon, vous apprendrez la syntaxe de LEFT JOIN, comment trouver les lignes sans correspondance, et l'erreur de WHERE qui transforme silencieusement un LEFT JOIN en INNER JOIN.

SQL LEFT JOIN : garder toutes les lignes de la table de gauche

INNER JOIN supprime les lignes sans correspondance. Or ce sont souvent précisément celles dont vous avez besoin : les films sans exemplaire en stock, les clients sans commande ce mois-ci, les produits jamais vendus. LEFT JOIN les conserve.

Ce que renvoie LEFT JOIN

• Chaque ligne de la table de gauche (celle qui suit FROM) apparaît au moins une fois dans le résultat.
• Si une ligne a des correspondances dans la table de droite, elle est combinée avec chacune d'elles, comme avec INNER JOIN.
• Si une ligne n'a pas de correspondance, elle apparaît une fois, et toutes les colonnes de la table de droite valent NULL.

LEFT JOIN et LEFT OUTER JOIN sont identiques ; le mot OUTER est facultatif.

Syntaxe de base

SELECT left_table.column1, right_table.column2
FROM left_table
LEFT JOIN right_table ON left_table.key_column = right_table.key_column;

Les films et leurs exemplaires

Sakila compte 1000 films, mais tous ne sont pas en stock. Avec LEFT JOIN, les films sans exemplaire restent dans le résultat :

SELECT f.film_id, f.title, i.inventory_id, i.store_id
FROM film AS f
LEFT JOIN inventory AS i ON i.film_id = f.film_id
ORDER BY i.inventory_id IS NULL DESC, f.title
LIMIT 10;

Résultat : les films sans exemplaire viennent en premier ; leurs inventory_id et store_id valent NULL.

Avec INNER JOIN, la même requête perdrait ces 42 films.

Trouver les lignes sans correspondance

Pour ne trouver que les lignes sans correspondance, ajoutez une condition WHERE qui teste une colonne de la table de droite à NULL. Testez la colonne clé de la table de droite : elle ne vaut NULL que lorsqu'aucune ligne n'a été trouvée.

SELECT f.film_id, f.title
FROM film AS f
LEFT JOIN inventory AS i ON i.film_id = f.film_id
WHERE i.inventory_id IS NULL
ORDER BY f.title;

Résultat : les 42 films qui n'ont d'exemplaire dans aucun magasin.

Ce modèle s'appelle une anti-jointure. Il répond aux questions du type « qu'est-ce qui manque ? ». Autre exemple, les exemplaires qui n'ont jamais été loués :

SELECT i.inventory_id, f.title, i.store_id
FROM inventory AS i
INNER JOIN film AS f ON f.film_id = i.film_id
LEFT JOIN rental AS r ON r.inventory_id = i.inventory_id
WHERE r.rental_id IS NULL;

Résultat : un exemplaire qui n'a jamais été loué.

Le piège du WHERE

Un filtre sur la table de droite placé dans WHERE supprime les lignes où les colonnes de cette table valent NULL, et le LEFT JOIN se comporte alors comme un INNER JOIN. Comparez deux requêtes : toutes deux doivent lister tous les clients avec leurs paiements effectués à partir du 23 août 2005.

-- Faux : les clients sans ces paiements disparaissent
SELECT c.customer_id, c.last_name, p.amount, p.payment_date
FROM customer AS c
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
WHERE p.payment_date >= '2005-08-23'
ORDER BY c.customer_id;

-- Juste : la condition fait partie de la jointure
SELECT c.customer_id, c.last_name, p.amount, p.payment_date
FROM customer AS c
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
                      AND p.payment_date >= '2005-08-23'
ORDER BY c.customer_id;

Résultat : la première requête ne renvoie que les clients qui ont payé pendant cette période. La seconde conserve les 599 clients ; ceux sans paiement fin août ont NULL dans amount et payment_date.

La règle : les conditions sur la table de droite qui doivent limiter les correspondances vont dans ON. Les conditions sur la table de gauche, qui doivent retirer des lignes du résultat, vont dans WHERE. La seule condition sur la table de droite à mettre dans WHERE est IS NULL pour une anti-jointure.

L'ordre des tables compte

Avec INNER JOIN, permuter les tables ne change pas le résultat. Avec LEFT JOIN, si : la table qui suit FROM est celle dont toutes les lignes sont conservées. film LEFT JOIN inventory garde tous les films ; inventory LEFT JOIN film garde tous les exemplaires, et chaque exemplaire a un film, donc les films sans exemplaire sont perdus.

Points clés :

• LEFT JOIN conserve chaque ligne de la table de gauche ; les lignes sans correspondance reçoivent NULL dans les colonnes de droite.
• LEFT JOIN et LEFT OUTER JOIN sont identiques.
• LEFT JOIN ... WHERE right_key IS NULL trouve les lignes sans correspondance (anti-jointure).
• Un filtre sur la table de droite dans WHERE transforme un LEFT JOIN en INNER JOIN ; placez-le dans ON.
• La table qui suit FROM est celle dont les lignes sont conservées.

Questions fréquentes

Quelle est la différence entre INNER JOIN et LEFT JOIN ?

INNER JOIN ne renvoie que les lignes qui ont une correspondance dans les deux tables. LEFT JOIN renvoie aussi les lignes de la table de gauche sans correspondance, en remplissant les colonnes de droite avec NULL.

LEFT JOIN est-il identique à LEFT OUTER JOIN ?

Oui. OUTER est facultatif, et les deux formes renvoient le même résultat dans tous les SGBD.

Pourquoi mon LEFT JOIN fonctionne-t-il comme un INNER JOIN ?

Très probablement, la clause WHERE contient une condition sur une colonne de la table de droite. Pour les lignes sans correspondance, cette colonne vaut NULL, la condition n'est pas vraie et les lignes sont supprimées. Déplacez la condition dans la clause ON.