OUTER JOIN : trouver les « écarts »

Dans le module 3, vous avez vu LEFT JOIN : conserver toutes les lignes à gauche, même celles sans correspondance à droite (qui deviennent NULL). Dans ce chapitre, nous élargissez la boîte à outils avec RIGHT JOIN et FULL OUTER JOIN, et nous apprenons comment les utiliser pour trouver les "trous" dans les données : les lignes d'un tableau qui n'ont pas d'équivalent dans l'autre.

Les trois types de OUTER JOIN

A LEFT  JOIN B  ON …  -- tutte le righe di A, con o senza match in B
A RIGHT JOIN B  ON …  -- tutte le righe di B, con o senza match in A
A FULL  JOIN B  ON …  -- tutte le righe di A *e* di B; NULL dove non c'è match

A RIGHT JOIN B peut toujours être réécrit en B LEFT JOIN A avec le même ON : c'est une question de lisibilité. De nombreux styles (y compris ce cours) préférez toujours utiliser LEFT JOIN pour des raisons de cohérence.

Le pattern "anti-join" : retrouver ceux qui manquent

L'utilisation la plus courante de LEFT JOIN n'est pas « d'enrichir avec les données sur le à droite", mais trouvez les lignes de gauche qui n'ont PAS de correspondance sur le c'est vrai. C'est ce qu'on appelle un anti-join :

-- Prodotti senza nessuna recensione:
SELECT p.id, p.name
FROM   products p
LEFT JOIN reviews r ON r.product_id = p.id
WHERE  r.id IS NULL
ORDER BY p.id;

Logique du modèle :

1. LEFT JOIN joint les avis de chaque produit, le cas échéant ;
2. pour les produits sans avis, toutes les colonnes r sont des NULL ;
3. WHERE r.id IS NULL conserve uniquement ces produits.

Contrôle de clé : un id IS NULL sur une colonne NOT NULL du tableau de droite signifie "il n'y avait pas de correspondance". Vous filtrez souvent sur le PK de la droite table précisément pour cette raison.

JOINTURE EXTERNE COMPLÈTE

FULL OUTER JOIN garde les deux côtés sans correspondance. C'est rarement utilisé, généralement pour les contrôles d'intégrité des données :

SELECT c.id AS customer_id, o.id AS order_id
FROM   customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id
WHERE  c.id IS NULL OR o.id IS NULL;

Les lignes avec c.id IS NULL seraient des commandes orphelines (un customer_id qui n'existe pas dans customers). Ceux qui ont o.id IS NULL sont des clients sans commandes. Sur notre jeu de données les clés étrangères garantissent qu'aucun orphelin des commandes existent ; il ne reste que les clients sans commandes.

Essayez-le vous-même

Exercice de révision