#JOIN : combiner des tablesCopier le lien

Combiner des lignes de plusieurs tables par des clés correspondantes reconstruit l'information dispersée entre les tables. Le choix du type de jointure contrôle quelles lignes sont conservées et comment les absences sont représentées (NULL). Savoir anticiper la cardinalité évite les doublons et les explosions de lignes.

#Prérequis et objectifsCopier le lien

• Prérequis : la section SELECT (ordre logique, alias, NULL) et les clés primaires/étrangères vues en Modélisation.
• Choisir le bon type de jointure (INNER, LEFT, RIGHT, FULL) selon la question posée.
• Prévoir le nombre de lignes produites, donc l'effet d'une agrégation en aval.
• Utiliser EXISTS / NOT EXISTS (semi et anti-jointure) pour filtrer sans dupliquer.

#Le modèle mentalCopier le lien

Table de gauche L, table de droite R :

1INNER:  L ∩ R   → seulement les correspondances2LEFT:   L ⟕ R   → tout L, colonnes de R si match, sinon NULL3RIGHT:  L ⟖ R   → tout R (équivalent à un LEFT les tables inversées)4FULL:   L ⟗ R   → tout L et tout R, NULL quand pas de match

#Les jointures vues par l'algèbreCopier le lien

Chaque type de jointure SQL a une définition formelle, et la connaître évite les surprises de cardinalité.

• Jointure thêta R ⋈_P S = σ_P(R × S) : la sélection du produit cartésien par le prédicat P, qui compare un attribut de R à un attribut de S avec l'un des opérateurs <, ≤, >, ≥, =, ≠. Un JOIN ... ON est une thêta-jointure.
• Équijointure : la thêta-jointure dont le prédicat est une égalité. C'est le cas de la quasi-totalité des jointures en pratique.
• Jointure naturelle R ∗ S : l'équijointure sur tous les attributs communs, en supprimant les occurrences multiples. JOIN ... USING (k) en est l'écriture SQL ; JOIN ... ON ne l'est pas, puisqu'il conserve les deux colonnes.
• Semi-jointure R ⋉ S : les tuples de R qui participent à la jointure avec S, sans les colonnes de S. C'est WHERE EXISTS, et c'est la raison pour laquelle EXISTS ne duplique jamais : le degré du résultat est celui de R.
• Jointure externe gauche : les tuples de R sans correspondance dans S sont conservés, les attributs de S mis à nul. C'est LEFT JOIN. La version droite conserve tout S, la version complète conserve les deux.
• Anti-jointure : les tuples de R sans correspondance dans S. SQL ne lui donne pas d'opérateur dédié : on l'écrit NOT EXISTS ou LEFT JOIN ... WHERE clé IS NULL.

Deux conséquences directes sur les cardinalités :

Retenez la ligne qui piège : une jointure externe gauche produit au moins autant de lignes que la table de gauche, et davantage si un tuple de gauche correspond à plusieurs tuples de droite. Un LEFT JOIN suivi d'un COUNT(*) sur la table de droite compte donc des correspondances, pas des utilisateurs — c'est la cause n°1 des totaux faux.

#Jeu de données fil rougeCopier le lien

1create table users(id integer primary key, name text not null);2create table orders(id integer primary key, user_id integer not null references users(id), amount real not null);3 4insert into users values (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');5insert into orders values (1, 1, 40.0), (2, 1, 60.0), (3, 2, 12.0);6-- Alice : 2 commandes ; Bob : 1 ; Charlie : 0

Deux requêtes à comparer mot à mot :

1-- INNER JOIN : uniquement les correspondances2select u.name, o.amount3from users u4join orders o on o.user_id = u.id5order by u.name;6-- Attendu : 3 lignes ; Alice/40, Alice/60, Bob/12 ; Charlie absent7 8-- LEFT JOIN : tous les utilisateurs, même sans commande9select u.name, o.amount10from users u11left join orders o on o.user_id = u.id12order by u.name;13-- Attendu : 4 lignes ; les 3 précédentes + Charlie/NULL

Une seule ligne de différence, mais une différence de sémantique : le INNER répond à « qui a commandé quoi », le LEFT répond à « que fait chaque utilisateur, même inactif ». Choisir, c'est d'abord décider ce que deviennent les absents.

#Animation : choisir la jointureCopier le lien

#Visualisation Venn (interactive)Copier le lien

Cliquez sur les zones de chaque diagramme pour voir quel type de jointure les conserve : la zone L seul disparaît en INNER, survit en LEFT, survit en FULL. Le composant affiche la requête SQL correspondante.

#Générateur de requêtesCopier le lien

Le générateur ci-dessous assemble une jointure à partir de vos choix : type, tables, colonne de lien. Utilisez-le pour vérifier la syntaxe de ON, puis rejouez la requête produite dans le playground ; l'objectif est de savoir écrire le squelette sans assistance.

select *
from users u
left join orders o on o.user_id = u.id;

#Filtrer la table droite en LEFT JOINCopier le lien

Piège classique et subtil :

1-- Condition dans ON : Charlie survit avec amount NULL2select u.name, o.amount3from users u4left join orders o on o.user_id = u.id and o.amount >= 505order by u.name;6-- Attendu : 3 lignes ; Alice/60, Bob/NULL (sa commande vaut 12), Charlie/NULL7 8-- Condition dans WHERE : Charlie disparaît9select u.name, o.amount10from users u11left join orders o on o.user_id = u.id12where o.amount >= 5013order by u.name;14-- Attendu : 1 ligne ; Alice/60 uniquement

#CTE : structurer les requêtes volumineusesCopier le lien

Les expressions de table commune (WITH) nomment des étapes intermédiaires. La requête reste déclarative, mais se lit comme un plan :

1with totals as (2  select user_id, sum(amount) as total3  from orders4  group by user_id5)6select u.name, coalesce(t.total, 0) as total7from users u8left join totals t on t.user_id = u.id9order by total desc;10-- Attendu : 3 lignes ; Alice 100.0, Bob 12.0, Charlie 0

L'agrégation a lieu dans la CTE, sur orders seul : le 1 vers N est déjà résorbé quand la jointure ramène un total par utilisateur. Agréger après la jointure donnerait le même résultat ici, mais exigerait un group by u.name et ferait le travail sur plus de lignes.

#Semi et anti-jointuresCopier le lien

Quand seule l'existence compte, on ne veut ni dupliquer les lignes de gauche, ni lire les colonnes de droite :

1-- SEMI : utilisateurs ayant au moins une commande (pas de doublon)2select u.* from users u3where exists (select 1 from orders o where o.user_id = u.id);4-- Attendu : 2 lignes, Alice et Bob5 6-- ANTI : utilisateurs sans aucune commande7select u.* from users u8where not exists (select 1 from orders o where o.user_id = u.id);9-- Attendu : 1 ligne, Charlie

Le LEFT JOIN ... WHERE o.user_id IS NULL réalise aussi l'anti-jointure ; NOT EXISTS exprime l'intention plus directement et reste correct si la colonne testée admet des NULL.

#PlaygroundCopier le lien

#Exercice : LEFT JOIN et placement du prédicatCopier le lien

Écrivez deux requêtes listant les utilisateurs et leurs commandes de 50 ou plus :

1. avec le filtre dans WHERE, puis constatez que Charlie (et tout utilisateur sous 50) disparaît ;
2. avec le filtre dans le ON du LEFT JOIN, puis constatez que tout utilisateur survit, colonnes d'orders à NULL.

Expliquez la différence de cardinalité (1 ligne contre 3 lignes) et la sémantique de chacune.

#Animation : exécution d'un JOINCopier le lien

#Mini-atelier de synthèseCopier le lien

Sur le jeu fil rouge, écrivez et vérifiez les cardinalités :

1. Total par utilisateur, 0 si aucune commande, tri décroissant (attendu : 3 lignes, Alice 100, Bob 12, Charlie 0).
2. Utilisateurs sans commande, de deux façons : LEFT JOIN ... WHERE o.user_id IS NULL et NOT EXISTS (attendu : 1 ligne, Charlie, deux fois).
3. Nombre d'utilisateurs ayant au moins une commande, sans se faire piéger par le 1 vers N :

1select count(*)2from users u3where exists (select 1 from orders o where o.user_id = u.id);4-- Attendu : 2

#Exercice : base MICRO (ventes d'un magasin)Copier le lien

Schéma : CLIENT(no_client, nom, ville), PRODUIT(no_produit, nom, marque, prix, quantite) — no_produit identifie un produit de manière unique — et VENTE(no_client, no_produit, date_vente, quantite), une vente étant identifiée par le triplet (no_client, no_produit, date_vente).

Écrivez en SQL :

1. le nom des clients qui ont acheté le produit numéro 202 ;
2. le numéro, le nom et la marque des produits non vendus ;
3. le numéro des clients ayant acheté au moins un exemplaire de chaque produit commercialisé.

1-- 1. Semi-jointure : les clients qui ont acheté le produit 2022select c.nom3from CLIENT c4where exists (select 1 from VENTE v where v.no_client = c.no_client and v.no_produit = 202);5-- Variante par jointure, qui exige DISTINCT pour ne pas répéter un client6select distinct c.nom7from CLIENT c join VENTE v on v.no_client = c.no_client8where v.no_produit = 202;9 10-- 2. Anti-jointure : les produits jamais vendus11select p.no_produit, p.nom, p.marque12from PRODUIT p13where not exists (select 1 from VENTE v where v.no_produit = p.no_produit);14-- Variante par différence ensembliste, exactement la traduction de l'algèbre

1-- Solution par comptage (correcte ici car la clé de VENTE interdit le doublon)2select v.no_client3from VENTE v4group by v.no_client5having count(distinct v.no_produit) = (select count(*) from PRODUIT);6 7-- Solution par double négation : la traduction directe de la division8select c.no_client9from CLIENT c10where not exists (11  select 1 from PRODUIT p12  where not exists (13    select 1 from VENTE v where v.no_client = c.no_client and v.no_produit = p.no_produit14  )

#QuizCopier le lien