Les Types de JOIN en SQL Expliqués avec des Exemples

Un JOIN mal écrit ne lève pas d’erreur. Il s’exécute, renvoie un résultat, et les lignes sont fausses : des clients manquants, des totaux gonflés, des enregistrements orphelins invisibles. La plupart du temps, la requête a obtenu exactement ce qu’elle demandait, et son auteur a choisi le mauvais type de join, ou filtré au mauvais endroit.

Un join combine les lignes de deux tables selon une condition que vous écrivez. Les lignes qui survivent dépendent du type de join, et les six types ci-dessous donnent six réponses différentes à partir des mêmes deux tables.

customers contient cinq personnes. Trois d’entre elles ont été parrainées par un autre client, enregistré dans referred_by :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        TEXT NOT NULL,
  referred_by INT REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_id, name, referred_by) VALUES
  (1, 'Alice', NULL),
  (2, 'Bob',   NULL),
  (3, 'Carol', 1),
  (4, 'Dave',  1),
  (5, 'Erin',  2);

orders contient six commandes. Cinq pointent vers un client réel. La sixième, 106, pointe vers le client 99, qui a été supprimé après avoir passé cette commande, donc la commande ne correspond plus à personne :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 1, 20.00),
  (103, 2, 75.00),
  (104, 4, 30.00),
  (105, 4, 15.00),
  (106, 99, 10.00);

Le plus rapide pour suivre les exemples est un conteneur Postgres, configuré comme dans Qu’est-ce que Docker Compose et Comment l’Utiliser. Les joins sont du SQL standard. Les deux endroits où MySQL se comporte différemment sont signalés plus bas.

INNER JOIN : seulement les lignes qui correspondent des deux côtés

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Cinq lignes. Carol et Erin n’ont pas de commande, donc elles disparaissent. La commande 106 n’a pas de client, donc elle disparaît aussi. JOIN et INNER JOIN veulent dire la même chose. INNER est facultatif et la plupart des gens l’omettent.

Sur cinq lignes, rien n’est lent. Sur une vraie table orders, orders.customer_id a besoin d’un index, sinon chaque join contre elle la parcourt entièrement. Comment Fonctionnent les Index de Base de Données explique quand cet index devient rentable.

LEFT JOIN : toutes les lignes de gauche, avec ou sans correspondance

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Tous les clients sont là. Carol et Erin reçoivent NULL dans les colonnes de commande. C’est le join que vous écrirez le plus souvent : « chaque X, avec son Y s’il en a un ».

RIGHT JOIN : toutes les lignes de droite, avec ou sans correspondance

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

La commande 106 revient, avec NULL à la place du nom. C’est la ligne orpheline que l’inner join cachait, et un right join (ou le left join équivalent) permet de la retrouver volontairement.

Vous verrez rarement un RIGHT JOIN dans du code réel. a RIGHT JOIN b renvoie les mêmes lignes que b LEFT JOIN a, donc la plupart des équipes utilisent LEFT JOIN partout et inversent l’ordre des tables. Réécrire un RIGHT JOIN ainsi est une question de cohérence. L’original n’est pas faux.

FULL OUTER JOIN : tout, des deux côtés

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name NULLS LAST, o.order_id;

Huit lignes : chaque correspondance, plus les clients sans correspondance, plus la commande sans correspondance. C’est ce qui en fait le join de la réconciliation, quand vous voulez tout ce qui, d’un côté ou de l’autre, ne s’aligne pas. En dehors de ce cas, vous n’en aurez pas souvent besoin.

MySQL n’a pas de FULL OUTER JOIN, et n’accepte pas non plus NULLS LAST. Pour obtenir les mêmes lignes là-bas, prenez le LEFT JOIN, puis ajoutez un UNION ALL avec un RIGHT JOIN filtré par WHERE c.customer_id IS NULL, pour que les lignes déjà trouvées ne soient pas répétées. Un simple UNION des deux joins fonctionne aussi, mais il paie le prix d’une déduplication et fusionne aussi des lignes qui sont réellement identiques.

CROSS JOIN : chaque ligne associée à chaque ligne

1
2
3

SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;

Cinq clients fois six commandes : trente lignes, sans aucune condition de join. La plupart des cross joins en production sont des accidents. Quelqu’un a écrit FROM customers, orders et a oublié le WHERE censé les relier. Vous en voudriez un volontairement pour générer des combinaisons, comme chaque taille avec chaque couleur, ou chaque jour d’une plage de dates avec chaque magasin, avant de remplir les vrais chiffres.

Comment fonctionne un self-join ?

customers.referred_by pointe vers une autre ligne de la même table. Pour afficher qui a parrainé qui par son nom, joignez la table à elle-même sous un second alias :

1
2
3
4

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
LEFT JOIN customers r ON c.referred_by = r.customer_id
ORDER BY c.name;

Le join fonctionne exactement comme avant. customers c et customers r sont deux alias, et le planificateur les traite comme deux tables distinctes qui contiennent par hasard les mêmes lignes. C’est un LEFT JOIN volontaire, pour qu’Alice et Bob, qui n’ont été parrainés par personne, restent dans le résultat. Toute table où une ligne pointe vers une autre ligne de la même table fonctionne ainsi : employés et managers, catégories et catégories parentes, commentaires et réponses.

Pourquoi une clause WHERE transforme-t-elle un LEFT JOIN en INNER JOIN ?

Vous voulez tous les clients, avec leurs commandes de plus de 25 $ quand ils en ont :

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25;

Carol et Erin ont disparu. WHERE s’exécute après le join, et pour elles o.amount vaut NULL. NULL > 25 n’est ni vrai ni faux. C’est inconnu, et WHERE ne garde que les lignes vraies. Le LEFT JOIN les avait gardées, et le filtre les a éliminées.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Garder tous les clients ; filtrer seulement les commandes qui comptent
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 25;

-- Garde les clients sans aucune commande, mais pas ceux dont toutes les commandes sont <= 25
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25 OR o.amount IS NULL;

Utilisez la première. Mettre la condition dans le ON décide quelles commandes ont le droit de correspondre, et chaque client garde au moins une ligne. La seconde version ne laisse passer que les lignes où le join n’a rien trouvé. Un client dont la seule commande est de 20 $ obtient une ligne avec amount = 20.00, qui ne remplit aucune des deux conditions, et ce client disparaît à nouveau. Sur ces données, les deux requêtes gardent par hasard les cinq clients, et c’est ainsi que la seconde passe les tests sans se faire remarquer.

Pourquoi SUM renvoie-t-il un total faux après avoir joint deux tables ?

Ajoutez une troisième table, payments, où une commande peut être payée en plusieurs fois :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  order_id   INT NOT NULL,
  amount     NUMERIC(10,2) NOT NULL
);

INSERT INTO payments (payment_id, order_id, amount) VALUES
  (1, 101, 25.00),
  (2, 101, 25.00),
  (3, 103, 75.00);

La commande 101 est de 50 $, payée en deux fois 25 $. Joignez maintenant les trois tables et totalisez ce que chaque client a commandé :

1
2
3
4
5

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.name;

Le vrai total d’Alice est de 70 $ (50.00 + 20.00). Deux choses ont mal tourné. La commande 101 correspond à deux paiements, donc le join renvoie deux fois cette ligne de commande et SUM(o.amount) additionne ses 50 $ deux fois. La commande 102 n’a pas de paiement, donc l’inner join vers payments l’élimine. Pour Alice, cela donne 50 + 50 + 0 = 100. Dave n’a aucun paiement, donc il n’apparaît pas dans le résultat.

La règle : quand vous agrégez une colonne, ne joignez pas les tables dont l’agrégation n’a pas besoin. Tout join un-à-plusieurs peut multiplier des lignes ou en supprimer. Si vous voulez seulement les totaux des commandes, laissez payments de côté :

1
2
3
4

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

Si vous avez aussi besoin des totaux de paiement dans le même rapport, faites d’abord un GROUP BY order_id sur payments dans une sous-requête ou une CTE. Cela donne une ligne par commande, que vous pouvez joindre aux commandes sans risque.

NOT IN contre NOT EXISTS : comment trouver les lignes sans correspondance ?

« Les clients sans commande » a tout l’air d’un travail pour NOT IN :

1
2

SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Cela ne fonctionne ici que parce que orders.customer_id est NOT NULL. Retirez cette contrainte, insérez une seule commande avec un customer_id à NULL, et la requête renvoie zéro ligne, sans aucune erreur. NOT IN se développe en une chaîne de comparaisons <> reliées par AND. x <> NULL est inconnu, donc un seul NULL rend la condition entière inconnue pour tous les clients.

Deux structures n’ont pas ce problème :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- LEFT JOIN + IS NULL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- NOT EXISTS
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Les deux renvoient Carol et Erin, et un NULL dans orders.customer_id n’affecte ni l’une ni l’autre. Aucune des deux ne construit de liste à comparer. Les deux vérifient, client par client, si une commande correspondante existe. Utilisez NOT EXISTS par défaut. Utilisez LEFT JOIN ... IS NULL quand la requête joint déjà les deux tables pour récupérer d’autres colonnes.

Quand un join renvoie plus de lignes que prévu, cherchez une table un-à-plusieurs entre le join et le GROUP BY. Quand il en renvoie moins, cherchez une condition WHERE sur la table qui peut être NULL. Dans les deux cas, corrigez la requête autour du join avant de changer le type de join.

Articles associés

• Comment Fonctionnent les Index de Base de Données
• Qu’est-ce que Docker Compose et Comment l’Utiliser
• Comment Fonctionne le Cache Redis et Comment l’Utiliser