02 - Les JOINs : INNER, LEFT, RIGHT, FULL et CROSS

Ce que tu vas apprendre

• Chaque type de JOIN avec des schemas ASCII et des requêtes reelles
• Quand utiliser quoi (et quand préférer une sous-requête)
• Les erreurs classiques et le coût en performance

Prerequisites

Avoir lu l'article 01 ou connaître SELECT/WHERE.

Les tables d'exemple

sqlCREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  total NUMERIC(10,2) NOT NULL,
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO customers (name, email) VALUES
  ('Alice', 'alice@mail.com'),
  ('Bob', 'bob@mail.com'),
  ('Charlie', 'charlie@mail.com'),
  ('Diana', 'diana@mail.com');

INSERT INTO orders (customer_id, total, status) VALUES
  (1, 150.00, 'completed'),
  (1, 89.50, 'pending'),
  (2, 230.00, 'completed'),
  (2, 45.00, 'cancelled'),
  (NULL, 99.99, 'pending');  -- commande sans client (import, migration...)

Alice a 2 commandes. Bob a 2 commandes. Charlie et Diana n'ont aucune commande. La commande #5 n'a pas de client.

INNER JOIN : l'intersection

Customers     Orders
+-------+    +-------+
| Alice |----| #1    |
| Bob   |----| #3    |
| Charlie|   | #5    |  (pas de client)
| Diana |    +-------+
+-------+
Resultat : seulement les lignes qui matchent des deux cotes

sqlSELECT c.name, o.id AS order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

 name  | order_id | total
-------+----------+--------
 Alice |        1 | 150.00
 Alice |        2 |  89.50
 Bob   |        3 | 230.00
 Bob   |        4 |  45.00

Charlie et Diana n'apparaissent pas (pas de commandes). La commande #5 non plus (pas de client). L'INNER JOIN ne garde que les lignes ou la condition ON est satisfaite des deux cotes.

LEFT JOIN : tout a gauche + ce qui matche a droite

sqlSELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

  name   | order_id | total
---------+----------+--------
 Alice   |        1 | 150.00
 Alice   |        2 |  89.50
 Bob     |        3 | 230.00
 Bob     |        4 |  45.00
 Charlie |   (null) |  (null)
 Diana   |   (null) |  (null)

Tous les clients apparaissent, meme ceux sans commande. Les colonnes de orders sont NULL quand il n'y a pas de correspondance. C'est le JOIN le plus utilise en pratique. Quand tu veux "tous les X avec leurs Y eventuels", c'est un LEFT JOIN.

Un pattern frequent pour trouver les clients SANS commande :

sqlSELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

  name
---------
 Charlie
 Diana

RIGHT JOIN : l'inverse du LEFT

sqlSELECT c.name, o.id AS order_id, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

 name  | order_id | total
-------+----------+--------
 Alice |        1 | 150.00
 Alice |        2 |  89.50
 Bob   |        3 | 230.00
 Bob   |        4 |  45.00
(null) |        5 |  99.99

La commande #5 (sans client) apparaît. Charlie et Diana non. En pratique, je n'utilise presque jamais RIGHT JOIN. Il suffit d'inverser l'ordre des tables et d'utiliser un LEFT JOIN. C'est plus lisible.

FULL OUTER JOIN : tout des deux cotes

sqlSELECT c.name, o.id AS order_id, o.total
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

  name   | order_id | total
---------+----------+--------
 Alice   |        1 | 150.00
 Alice   |        2 |  89.50
 Bob     |        3 | 230.00
 Bob     |        4 |  45.00
 Charlie |   (null) |  (null)
 Diana   |   (null) |  (null)
 (null)  |        5 |  99.99

Tout apparaît : les clients sans commande ET les commandes sans client. Utile pour les rapprochements de donnees (reconciliation entre deux sources), mais assez rare au quotidien.

CROSS JOIN : le produit cartesien

sqlSELECT c.name, o.id AS order_id
FROM customers c
CROSS JOIN orders o;

Ca généré 4 clients x 5 commandes = 20 lignes. Chaque client est associe a chaque commande. C'est rarement ce que tu veux. Mais c'est utile pour générer des combinaisons, par exemple toutes les paires possibles entre des produits et des tailles :

sqlSELECT p.name, s.label
FROM products p
CROSS JOIN sizes s;

Attention : un JOIN sans clause ON est implicitement un CROSS JOIN. C'est une source de bugs classique. Si tu oublies le ON, tu fais un produit cartesien par accident et ta requête retourne des millions de lignes.

Self JOIN : une table avec elle-meme

Pour les hierarchies. Imagine une table d'employes :

sqlCREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES
  ('CEO', NULL),
  ('CTO', 1),
  ('Dev Senior', 2),
  ('Dev Junior', 3);

SELECT e.name AS employe, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

   employe    | manager
--------------+---------
 CEO          | (null)
 CTO          | CEO
 Dev Senior   | CTO
 Dev Junior   | Dev Senior

La meme table utilisee deux fois avec des aliases différents. Pour les hierarchies profondes (arbres a N niveaux), il faut plutot utiliser WITH RECURSIVE, qu'on verra dans l'article 04.

Plusieurs JOINs dans une requête

C'est la que ca devient concret pour un backend. Une requête typique :

sqlCREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  product_name TEXT NOT NULL,
  quantity INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL
);

INSERT INTO order_items (order_id, product_name, quantity, unit_price) VALUES
  (1, 'Clavier', 1, 89.99),
  (1, 'Souris', 1, 59.99),
  (3, 'Ecran', 1, 349.00);

SELECT c.name, o.id AS order_id, oi.product_name, oi.quantity, oi.unit_price
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed';

 name  | order_id | product_name | quantity | unit_price
-------+----------+--------------+----------+------------
 Alice |        1 | Clavier      |        1 |      89.99
 Alice |        1 | Souris       |        1 |      59.99
 Bob   |        3 | Ecran        |        1 |     349.00

Deux JOINs, un WHERE. Ca, c'est exactement le genre de requête que Prisma généré quand tu fais un include avec des relations imbriquees.

JOIN vs sous-requête

Deux facons d'obtenir le meme résultat :

sql-- Avec un JOIN
SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

-- Avec une sous-requete
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

En général, PostgreSQL optimise les deux de la meme facon (le planificateur de requêtes est malin). Mais pour les requêtes complexes, le JOIN est souvent plus lisible et donne plus de contrôle. La sous-requête est parfois plus claire quand tu veux juste vérifier l'existence (avec EXISTS, voir l'article 04).

Résumé

• INNER JOIN : intersection, seulement les lignes qui matchent
• LEFT JOIN : le plus utilise, tout a gauche + ce qui matche a droite
• RIGHT JOIN : inverse du LEFT, rarement nécessaire
• FULL OUTER JOIN : tout des deux cotes, pour la reconciliation
• CROSS JOIN : produit cartesien, attention aux oublis de ON
• Self JOIN : une table avec elle-meme, pour les hierarchies simples

Article précédent : 01 - SELECT fondamentaux Article suivant : 03 - GROUP BY et aggregation

Sources

• PostgreSQL Documentation - Joins Between Tables
• Use The Index, Luke - Joins
• pganalyze - Understanding Join Performance