Jointures de tables (2) — OUTER JOIN, CROSS JOIN, auto-jointure

OUTER JOIN — garder les lignes qui n'existent que d'un seul côté

L'INNER JOIN de la dernière fois ne renvoyait que les lignes qui correspondaient dans les deux tables, donc les employés sans service et les services sans employé disparaissaient du résultat. Quand le besoin est « je veux tous les employés ; ceux sans service peuvent avoir un nom de service vide », tu utilises un OUTER JOIN qui garde toujours les lignes d'un côté.

Il existe 3 sortes d'OUTER JOIN selon le côté que tu gardes : LEFT JOIN garde toutes les lignes de la table de gauche, RIGHT JOIN garde toutes les lignes de la table de droite, et FULL OUTER JOIN garde toutes les lignes des deux. Les colonnes du côté sans correspondance sont remplies par NULL. La console de ce cours utilise SQLite, et tu peux exécuter LEFT / RIGHT / FULL tels quels. Les données sont les mêmes que la dernière fois : department (6 services) et employee (30 employés).

LEFT JOIN — toujours garder la table de gauche

Quand tu écris SELECT cols FROM left LEFT JOIN right ON condition, chaque ligne de la table de gauche est gardée, et s'il n'y a pas de ligne correspondante dans la table de droite, les colonnes de droite deviennent NULL. LEFT JOIN est l'abréviation de LEFT OUTER JOIN, et les deux se comportent pareil. Tu l'utilises quand tu veux lister le côté principal sans en perdre personne, comme « afficher tous les employés, et laisser le nom du service vide pour les personnes sans service ».

-- Garder tous les employés. S'il n'y a pas de service, dept_name est NULL
SELECT e.name, e.city, d.dept_name
FROM employee e
LEFT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

Imagine le besoin : « je veux trouver les employés qui n'appartiennent à aucun service, aligner le dept_id côté employé et côté service, et confirmer pourquoi le nom du service ne peut pas être retrouvé ». (Exécute-la correctement et l'explication apparaîtra.)

① LEFT JOIN employee (alias e) à gauche et department (alias d) à droite, et restreins aux seules lignes où d.dept_id est NULL.

② En plus de e.name et e.city, récupère `e.dept_id` (côté employé) et `d.dept_id` / `d.dept_name` (côté service). Confirme visuellement que, parce que e.dept_id est lui-même NULL (aucun service assigné), il n'y a pas de partenaire de jointure, donc d.dept_id et d.dept_name sont NULL aussi. Trie par e.name croissant.

③ Ensuite, change la même jointure en INNER JOIN et confirme que pas une seule ligne ne revient (parce que les lignes dont la clé de jointure est NULL disparaissent avec un INNER JOIN).

RIGHT JOIN et FULL OUTER JOIN

RIGHT JOIN est un LEFT JOIN avec la gauche et la droite inversées — il garde toutes les lignes de la table de droite. Quand tu écris employee e RIGHT JOIN department d ON ..., le service Legal sans aucun employé reste dans le résultat, et les colonnes du côté employee deviennent NULL. Il convient pour « afficher tous les services, et montrer les services sans employé avec des champs vides ».

FULL OUTER JOIN garde toutes les lignes des deux tables. Les lignes correspondantes sont jointes côte à côte, les lignes qui n'existent qu'à gauche (les 4 personnes sans service) reçoivent NULL du côté droit, et les lignes qui n'existent qu'à droite (le service Legal) reçoivent NULL du côté gauche — le tout aligné dans un seul résultat.

-- RIGHT JOIN: garder tous les services. Legal a NULL du côté employé
SELECT d.dept_name, e.name
FROM employee e
RIGHT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

-- FULL OUTER JOIN: garder à la fois les employés sans service et les services sans employé
SELECT e.name, d.dept_name
FROM employee e
FULL OUTER JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

Imagine le besoin : « je veux confirmer les services où pas un seul employé n'est assigné, en alignant les colonnes côté service et les colonnes côté employé ».

① RIGHT JOIN employee (alias e) à gauche et department (alias d) à droite, et restreins aux seules lignes où e.emp_id est NULL.

② Récupère d.dept_id / d.dept_name / d.location (côté service) et `e.emp_id` / `e.name` (côté employé). Confirme que d.dept_id a une valeur (le numéro du service) tandis que e.emp_id et e.name sont NULL (vides) — ce qui signifie que le service existe réellement mais a 0 employé assigné. Trie par d.dept_name croissant.

Imagine le besoin : « je veux confronter employés et services dans une seule table, aligner le dept_id des deux côtés, et confirmer comment les lignes qui n'existent que d'un seul côté deviennent NULL ».

① FULL OUTER JOIN employee (alias e) et department (alias d). La condition de jointure est une correspondance de dept_id.

② Récupère e.emp_id / e.name / `e.dept_id` (côté employé) et `d.dept_id` / `d.dept_name` (côté service). Les lignes correspondantes ont des valeurs des deux côtés, mais les employés sans service assigné ont à la fois `e.dept_id` et `d.dept_id` NULL, et le service sans employé (Legal) a une valeur dans `d.dept_id` et NULL du côté `e`. Confirme visuellement à quel motif appartient chaque « ligne d'un seul côté ».

③ Trie par d.dept_id croissant (les lignes où d.dept_id est NULL se regroupent vers le haut).

CROSS JOIN — construire toutes les combinaisons

CROSS JOIN n'a pas de condition de jointure et construit toutes les combinaisons de chaque ligne de la table de gauche avec chaque ligne de la table de droite. Le nombre de lignes du résultat est nombre de lignes à gauche × nombre de lignes à droite (le produit cartésien). Pour employee (30 lignes) et department (6 lignes), ça fait 30 × 6 = 180 lignes.

En pratique tu l'utilises pour énumérer toutes les combinaisons possibles comme « tous les employés × tous les mois » ou « tous les magasins × tous les produits », puis tu y joins les valeurs réelles et tu remplis avec 0 les cases sans valeur réelle — comme base d'une agrégation. Comme c'est une jointure sans condition, tu n'ajoutes pas de ON.

-- Compter le nombre de combinaisons tous-employés x tous-services
SELECT count(*) AS combo_count
FROM employee e
CROSS JOIN department d;

-- Inspecter une partie des combinaisons (seulement les paires avec le service Sales)
SELECT e.name, d.dept_name
FROM employee e
CROSS JOIN department d
WHERE d.dept_name = 'Sales'
ORDER BY e.emp_id;

Imagine le besoin : « je veux compter combien de combinaisons possibles il y a entre tous les employés et tous les services ».

① CROSS JOIN employee (alias e) et department (alias d) (n'écris pas de condition de jointure).

② Compte le nombre total de combinaisons avec count(*), et donne à la colonne de résultat l'alias combo_count.

Auto-jointure — utiliser la même table deux fois pour retrouver le nom du manager

La table employee a une colonne manager_id (le numéro d'employé du manager), et le manager est lui aussi un employé dans la même table employee. Quand tu veux « afficher le nom de chaque employé à côté du nom de son manager », tu JOIN la même table deux fois. C'est ce qu'on appelle une auto-jointure.

Tu l'écris comme FROM employee e JOIN employee m ON e.manager_id = m.emp_id. Tu fais apparaître la même table deux fois avec des alias qui séparent les rôles — e comme « le côté employé » et m comme « le côté manager ». Les alias sont obligatoires dans une auto-jointure ; sans eux tu ne peux pas dire quel employee est lequel. Avec un INNER JOIN, les employés sans manager disparaissent, donc utilise un LEFT JOIN si tu veux lister tous les employés.

-- Afficher le nom du manager de chaque employé à côté (seulement les employés ayant un manager : INNER)
SELECT e.name AS employee, m.name AS manager
FROM employee e
JOIN employee m
  ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

Imagine le besoin : « je veux lister tous les employés avec le nom de leur manager à côté. Les employés sans manager peuvent avoir un nom de manager vide ».

① LEFT JOIN employee comme alias e (côté employé) avec employee à nouveau comme alias m (côté manager). La condition de jointure est e.manager_id = m.emp_id.

② Récupère 2 colonnes, en donnant à e.name l'alias employee et à m.name l'alias manager.

③ Trie par e.emp_id croissant.

Le NULL d'un OUTER JOIN change de sens selon où tu places le WHERE

Si après un LEFT JOIN tu écris une condition sur une colonne du côté droit comme WHERE right_table.column = value, NULL est toujours évalué comme faux dans une comparaison, donc les lignes sans partenaire tombent et tu obtiens en pratique le même résultat qu'un INNER JOIN. Quand tu veux restreindre tout en gardant « les lignes sans partenaire », écris cette condition du côté ON, ou utilise WHERE right_table.key IS NULL pour récupérer explicitement « les lignes sans partenaire ». Pour les tests de NULL, utilise toujours IS NULL / IS NOT NULL, jamais = NULL.