Combiner des tables : JOIN

La jointure de base

Les commandes stockent customer_id. Les clients stockent le nom. Pour les voir ensemble, indiquez à la base comment les deux tables se correspondent.

SELECT o.order_number,
       o.ordered_at,
       o.total,
       c.first_name || ' ' || c.last_name AS customer,
       c.country
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.ordered_at DESC
LIMIT 20;

Trois éléments méritent d’être nommés. JOIN seul signifie INNER JOIN. ON c.id = o.customer_id est la condition de jointure — comment une ligne de gauche est appariée à une ligne de droite. Et orders o donne à la table un alias court, ce qui permet d’écrire o.total et c.country sans répéter le nom complet.

Les alias cessent d’être optionnels dès que deux tables ont une colonne du même nom. Ces deux tables ont chacune un id ; non qualifié, id serait ambigu et la base refuserait la requête.

Plus de deux tables

Les jointures s’enchaînent. Les produits d’une commande sont à deux pas : orders → order_items → products.

SELECT o.order_number,
       p.name AS product,
       oi.quantity,
       oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'delivered'
ORDER BY o.ordered_at DESC
LIMIT 25;

Chaque JOIN accroche une table de plus à ce que vous avez déjà : une jointure sur quatre tables, c’est la même chose avec une ligne en plus. Lisez de haut en bas : « les commandes, avec leurs lignes de commande, avec le produit de chaque ligne ».

Garder les lignes sans correspondance

Une jointure interne écarte tout ce qui n’a pas de partenaire. Un client qui n’a jamais commandé disparaît simplement du résultat. LEFT JOIN garde chaque ligne de la table de gauche et remplit la droite avec des NULL là où il n’y avait rien à apparier.

SELECT c.id,
       c.first_name,
       c.last_name,
       o.order_number,
       o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id
LIMIT 25;

C’est ce qui rend possible la requête « trouver ce qui manque » : tout garder, puis ne conserver que les lignes dont l’appariement a échoué.

-- The 95 customers who have never ordered anything.
SELECT c.id, c.first_name, c.last_name, c.country
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id
LIMIT 25;

WHERE o.id IS NULL se lit bizarrement au début — orders.id est une clé primaire et ne peut jamais être nulle dans la table. Mais elle peut l’être dans ce résultat, parce que le LEFT JOIN a inventé ces NULL pour les lignes non appariées. Ces lignes sont les 95 clients qui n’ont jamais commandé.

ON ou WHERE : ce n’est pas la même question

Voici le piège. Les deux requêtes mentionnent status = 'cancelled'. Une seule est réellement un LEFT JOIN.

-- The condition is part of the join: every customer is kept,
-- and only cancelled orders are attached to them.
SELECT c.id, c.first_name, o.order_number, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'cancelled'
ORDER BY c.id
LIMIT 25;

La condition est dans ON, elle fait donc partie de l’appariement : chaque client est conservé, et une commande annulée est rattachée quand il en existe une. Les clients sans commande annulée apparaissent quand même, avec des NULL.

-- The same condition in WHERE runs after the join and throws away
-- every row where it is not true — including the NULLs the LEFT JOIN made.
SELECT c.id, c.first_name, o.order_number, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'cancelled'
ORDER BY c.id
LIMIT 25;

Ici la condition est dans WHERE, qui s’exécute après la jointure et jette toutes les lignes pour lesquelles elle n’est pas vraie. Les lignes NULL que le LEFT JOIN vient de créer échouent au test — NULL = 'cancelled' n’est pas vrai — et sont donc toutes écartées. Vous avez écrit un LEFT JOIN et obtenu un INNER JOIN.

La règle est courte : une condition sur la table de droite d’un LEFT JOIN va dans ON. Une condition sur la table de gauche va dans WHERE. Pour une jointure interne, cela ne change réellement rien, et c’est pour cela que la mauvaise habitude s’attrape facilement.

Joindre une table à elle-même

Rien n’impose que les deux côtés soient des tables différentes. Les employés ont un manager_id qui pointe vers un autre employé : la table se joint donc à elle-même sous deux alias différents.

SELECT e.first_name || ' ' || e.last_name AS employee,
       e.position,
       m.first_name || ' ' || m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY manager, employee
LIMIT 25;

e et m sont la même table vue deux fois, et ce sont les alias qui rendent la chose possible. C’est un LEFT JOIN parce que les dix personnes au sommet n’ont pas de responsable, et qu’une jointure interne les laisserait discrètement de côté.

Ce que vous savez faire maintenant

Vous pouvez poser des questions sur tout le schéma. Ce que vous ne savez pas encore faire, c’est résumer la réponse : compter les commandes, totaliser le chiffre d’affaires et calculer des moyennes, c’est la prochaine leçon.