Jointures SQL et associations entre tables

L’adresse d’un client est associée au client. La date d’une commande est associée à la commande. Conserver ces informations dans des tables distinctes réduit les répétitions, mais un rapport a souvent besoin des deux. Une jointure combine leurs lignes dans une requête.

Lire les clés

Une clé primaire identifie une ligne. Elle peut être constituée d’une colonne ou de plusieurs colonnes réunies. Une clé étrangère fait référence à une clé primaire ou à une autre clé unique, dans une autre table ou dans la même table. Dans la base du magasin, orders.customer_id pointe vers customers.customer_id.

Les commandes 101 et 102 font toutes deux référence à la cliente 1, Ana. La commande 103 fait référence à Ben. La commande 104 a été passée sans compte : sa clé client est NULL, elle ne fait donc référence à aucun client. Le numéro de commande identifie la commande ; il n’identifie pas le client. Joindre order_id à customer_id reviendrait à comparer des identifiants sans lien.

Nommer l’association

L’association entre clients et commandes est un-à-plusieurs. Un client peut n’avoir aucune commande, en avoir une ou plusieurs. Cela ne signifie pas que la table des clients stocke un identifiant de commande pour chaque achat. Chaque commande stocke sa propre référence au client.

Pour les étudiants et les cours, aucun des deux côtés ne peut contenir une seule référence pour décrire tous les liens. Une ligne de enrollments enregistre une paire étudiant-cours. Plusieurs lignes d’inscription peuvent faire référence au même étudiant, et plusieurs peuvent faire référence au même cours.

Tracer un chemin avant d’écrire du SQL

Pour lister les produits achetés par chaque client, suis customers → orders → order_items → products. Les liens utilisent customer_id, puis order_id, puis product_id. Chaque lien nécessite sa propre condition. Une ligne de commande fournit le lien manquant entre une commande et un produit.

Lis la signification du schéma, sans te limiter aux noms des colonnes. employees.manager_id peut faire référence à employees.emp_id, même si les noms diffèrent. À l’inverse, customers.name et products.name désignent des choses de nature différente et ne doivent pas être mis en correspondance simplement parce qu’ils portent tous deux le nom name.

Une clé étrangère déclarée est un guide utile, mais SQL peut aussi joindre des tables sans contrainte déclarée. Un prix peut correspondre à une tranche de prix, et deux employés peuvent correspondre parce qu’ils ont le même responsable. La condition doit exprimer l’association nécessaire pour répondre à la question.

Une jointure construit de nouvelles lignes en combinant des lignes d’entrée. Pars de la règle de correspondance, puis demande-toi combien de correspondances possède chaque ligne. La réponse détermine les lignes qui apparaissent et le nombre de répétitions de leurs valeurs.

Raisonner par paires

Pour une jointure interne, imagine chaque client à côté de chaque commande. Ne conserve une paire que lorsque o.customer_id = c.customer_id est vrai. La commande 101 est associée à Ana ; elle n’est pas associée à Ben, Chloe ou Dev. La commande 102 est aussi associée à Ana. Un client peut donc contribuer à plusieurs lignes du résultat.

La condition peut être vraie, fausse ou inconnue. Une égalité faisant intervenir NULL est inconnue : la commande 104 passée sans compte ne trouve donc aucun client correspondant. Une jointure externe conserve aussi les lignes sans correspondance du côté choisi et remplit les colonnes de la ligne manquante avec NULL.

Cette représentation par paires explique mieux les jointures que des cercles qui se chevauchent. Une jointure ne se contente pas de réunir des éléments partagés : elle construit des lignes combinées et des clés répétées peuvent produire plus de lignes que n’en contient chacune des tables d’entrée. Si deux lignes d’un côté et trois de l’autre partagent une clé, cette clé donne six paires correspondantes.

Choisir les colonnes indépendamment des correspondances

La table des clients du magasin comporte trois colonnes et celle des commandes en comporte quatre. Avec JOIN ... ON, SELECT * renvoie les sept colonnes, y compris les deux exemplaires de customer_id. Tu peux aussi sélectionner uniquement les champs nécessaires au rapport :

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

Le résultat est (Ana, 101, 22.00), (Ana, 102, 25.50) et (Ben, 103, 60.00). Les colonnes de clé servent à établir les correspondances sans devoir apparaître dans le résultat sélectionné. Sélectionner moins de colonnes change le nombre de colonnes du résultat, mais pas les paires de lignes d’entrée qui correspondent.

Donner aux tables des noms courts et clairs

Dans FROM customers c, c est un alias de table. Désigne les colonnes de cette table par c.name et c.customer_id. L’expression c.name est un nom de colonne qualifié : la partie avant le point indique quel rôle de table fournit la valeur.

Les deux tables jointes contiennent customer_id. Écrire seulement SELECT customer_id avec la forme ON ne permet pas à la base de données de choisir entre les deux et provoque une erreur de colonne ambiguë. Écris c.customer_id ou o.customer_id selon la valeur voulue. Qualifier les autres colonnes sélectionnées permet aussi de voir facilement leur origine.

AS avant un alias de table est facultatif dans PostgreSQL, SQLite, MySQL et SQL Server. Oracle ne l’accepte pas : FROM customers c est donc une forme commune utile. Après avoir donné un alias à une table, utilise-le de façon cohérente.

Lire l’ordre logique de la requête

FROM et ses jointures construisent les lignes combinées. WHERE filtre ces lignes. SELECT choisit les colonnes à afficher, puis ORDER BY trie le résultat. Un filtre peut utiliser une colonne jointe même si cette colonne n’est pas sélectionnée. Par exemple, WHERE c.city = 'Denver' peut filtrer un rapport qui n’affiche que les noms et les totaux des commandes.

Sans ORDER BY, la base de données ne garantit aucun ordre particulier des lignes. Une requête qui affiche Ana en premier aujourd’hui pourra afficher une autre ligne en premier plus tard ; le tri est une instruction distincte de la correspondance.

Utilise une jointure interne lorsqu’une ligne du résultat nécessite une correspondance dans les deux tables. JOIN et INNER JOIN signifient la même chose. Aucun des deux ne conserve une ligne qui ne trouve pas de correspondance.

Construire la requête en trois parties

Nomme d’abord les tables et leurs rôles. Indique ensuite l’association dans ON. Choisis enfin les colonnes à afficher.

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

Lis la condition ainsi : « la référence au client de la commande est égale à l’identifiant de ce client ». Pour la commande 101, la référence est 1 : elle est donc associée à Ana. La commande 102 porte aussi la référence 1, et la commande 103 porte la référence 2. Voici le résultat complet :

Ana apparaît deux fois parce qu’elle a deux commandes correspondantes. Chloe et Dev n’en ont aucune et n’apparaissent donc pas. La commande 104, passée sans compte, a une référence client NULL, qui ne satisfait aucune égalité avec une clé client.

Filtrer et trier les lignes combinées

Pour afficher uniquement les clients de Denver, place WHERE c.city = 'Denver' après la jointure et avant ORDER BY. Le résultat devient (Ana, 101, 22.00) et (Ana, 102, 25.50). Chloe habite aussi à Denver, mais la jointure interne n’a produit aucune ligne pour elle.

Pour une jointure interne, placer ce filtre dans ON donne les mêmes paires. Une convention lisible consiste à garder les conditions de correspondance des tables dans ON et les filtres du rapport dans WHERE. Cette équivalence ne s’étend pas aux jointures externes.

Tu peux trier selon une colonne jointe absente du résultat sélectionné. Tu peux aussi omettre les clés de jointure du résultat : la requête ci-dessus effectue la correspondance sur customer_id tout en affichant un nom, un numéro de commande et un total. Le rôle d’une colonne dans la correspondance est distinct de son rôle dans le rapport.

Faire correspondre toutes les parties d’une clé

Certains enregistrements sont identifiés par une paire de colonnes. Une ligne d’expédition qui fait référence à une ligne de commande peut nécessiter à la fois l’identifiant de commande et celui du produit :

SELECT s.order_id, s.product_id, oi.quantity
FROM shipment_lines s
JOIN order_items oi
  ON s.order_id = oi.order_id
 AND s.product_id = oi.product_id;

Les deux comparaisons doivent être vraies pour obtenir une correspondance. Une correspondance sur order_id seul rattacherait chaque ligne d’expédition à chaque ligne de produit de cette commande. La requête pourrait s’exécuter sans erreur tout en indiquant des quantités de produits incorrectes.

Changer l’ordre sans changer le sens

Écrire FROM orders o JOIN customers c ON c.customer_id = o.customer_id donne les mêmes paires client-commande. Inverser les deux côtés de = conserve aussi l’égalité. SELECT * peut disposer les colonnes différemment après permutation des tables, et l’ordre des lignes reste indéterminé sans tri.

Une modification bien plus grave consiste à écrire ON o.order_id = c.customer_id. Les deux colonnes contiennent des nombres, c’est donc une instruction SQL valide, mais elles identifient des choses différentes. Dans les données du magasin, elle ne renvoie aucune ligne : les identifiants de commande 101–104 ne sont jamais égaux aux identifiants de client 1–4. Vérifie l’association dans le schéma même lorsque la base de données ne signale aucune erreur de syntaxe.

ON rend une condition explicite, mais les requêtes SQL existantes peuvent utiliser des formes plus courtes ou plus anciennes. Lis chaque forme en te demandant quelles paires elle conserve. Certaines formes changent aussi la façon dont les colonnes communes apparaissent dans le résultat.

USING nomme la clé commune

Lorsque les colonnes de clé portent le même nom, cette requête utilise une égalité abrégée :

SELECT c.name, o.order_id
FROM customers c
JOIN orders o USING (customer_id)
ORDER BY o.order_id;

Elle renvoie (Ana, 101), (Ana, 102) et (Ben, 103). USING (customer_id) signifie que les deux colonnes customer_id sont comparées. Avec SELECT *, la requête renvoie six colonnes en affichant la clé commune une seule fois ; JOIN ... ON inclut les deux colonnes de clé d’entrée.

Pour une clé composite, liste les deux noms : USING (order_id, product_id). Chaque colonne indiquée doit exister sous ce nom dans les deux tables d’entrée.

PostgreSQL, SQLite, MySQL et Oracle prennent en charge USING ; SQL Server ne le prend pas en charge. Dans Oracle, une colonne nommée dans USING doit être utilisée sans qualificatif : écris donc SELECT customer_id, et non SELECT c.customer_id. Les autres colonnes peuvent toujours utiliser leurs alias de table.

NATURAL JOIN choisit les colonnes à ta place

Un NATURAL JOIN compare tous les noms de colonnes communs aux tables d’entrée, combine ces tests d’égalité avec AND et affiche les colonnes communes une seule fois. Il est pris en charge par PostgreSQL, SQLite, MySQL et Oracle, mais pas par SQL Server.

Ce choix automatique peut modifier une requête sans avertissement. Les clients et les commandes n’ont actuellement que customer_id en commun. Ajouter une colonne city aux commandes amène une jointure naturelle à exiger une correspondance à la fois sur l’identifiant client et sur la ville. Une ville de livraison différente de celle du client peut alors exclure une commande qui correspondait auparavant.

Les clients et les produits possèdent tous deux name, mais l’un désigne une personne et l’autre un produit. Leur jointure naturelle comparerait ces noms sans lien et ne renvoie aucune ligne dans les données du magasin. En l’absence de noms de colonnes communs, une jointure naturelle renvoie toutes les combinaisons. Utilise ON ou une liste USING choisie volontairement pour que la règle de correspondance reste visible.

Lire les anciennes jointures par virgule

SELECT c.name, o.order_id
FROM customers c, orders o
WHERE o.customer_id = c.customer_id;

Cette requête renvoie les trois mêmes paires que la jointure interne. La condition se trouve ici dans WHERE. Si tu l’oublies, les quatre clients sont associés aux quatre commandes, ce qui produit 16 lignes. JOIN ... ON garde chaque table à côté de sa propre condition, ce qui facilite la vérification des requêtes plus longues.

Faire correspondre une valeur à une plage

Une équijointure utilise une égalité. Une non-équijointure utilise une autre comparaison, comme un test de plage :

SELECT p.name, b.band
FROM products p
JOIN price_bands b
  ON p.price BETWEEN b.min_price AND b.max_price;

Les tranches sont Budget 0,00–4,99, Standard 5,00–19,99 et Premium 20,00–999,99. Mug à 8,00 et Stapler à 12,00 correspondent à Standard ; Pen à 1,50 et Notebook à 4,25 correspondent à Budget. Premium ne correspond à aucun produit et ne contribue à aucune ligne.

BETWEEN inclut les deux bornes. Une nouvelle tranche Gift allant de 8,00 à 15,00 donnerait une seconde correspondance à Mug et à Stapler. L’écart entre 4,99 et 5,00 laisserait un prix de 4,995 sans correspondance. Les plages déterminent le résultat tout comme les clés : une valeur peut avoir zéro, une ou plusieurs correspondances.

Un rapport qui demande « tous les clients, y compris ceux sans commande » nécessite une jointure externe gauche. LEFT JOIN conserve chaque ligne de gauche. Il y rattache chaque ligne de droite correspondante ou fournit une ligne de valeurs NULL lorsqu’il n’y a pas de correspondance. Le mot OUTER est facultatif.

Suivre la table conservée

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

Ana fournit toujours deux lignes. Une jointure gauche ne réduit pas les commandes d’un client à une seule. Chloe et Dev fournissent chacun exactement une ligne, car aucun des deux n’a de correspondance. Il y a cinq lignes au total : trois paires correspondantes et deux clients sans correspondance.

Avant les filtres ultérieurs, la jointure renvoie au moins autant de lignes que la table de gauche. Pour chaque ligne de gauche, compte ses correspondances ; si elle n’en a aucune, compte une ligne complétée par des NULL. Cela explique pourquoi quatre clients peuvent produire cinq lignes.

La commande 104 passée sans compte est absente. Conserver tous les clients ne conserve pas aussi les commandes sans correspondance. Pour conserver toutes les commandes, commence par orders o LEFT JOIN customers c ON c.customer_id = o.customer_id. Sélectionner ensuite o.order_id, c.name donne (101, Ana), (102, Ana), (103, Ben) et (104, NULL). Chloe et Dev n’apparaissent plus.

Trouver les lignes sans correspondance

Une anti-jointure gauche ne conserve que les lignes qui n’ont pas trouvé de correspondance :

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

La jointure gauche crée d’abord les cinq lignes. WHERE conserve ensuite Chloe et Dev, dont les clés de commande sont NULL. C’est précisément l’absence de correspondance que recherche la requête.

Choisis une colonne du côté droit qui ne peut pas être NULL dans une véritable correspondance. La clé primaire d’une commande convient bien à ce test. Tester un coupon_code facultatif conserverait aussi des commandes réellement correspondantes mais sans coupon : cela ne signifierait donc pas « aucune commande ». Utilise IS NULL : = NULL donne une valeur inconnue et ne conserve aucune ligne dans WHERE.

Le même procédé permet de trouver les produits invendus :

SELECT p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_id IS NULL;

La requête renvoie (Stapler), le seul produit sans ligne de commande. Ici, une ligne correspondante réelle possède nécessairement un identifiant de commande : le test détecte donc la ligne manquante.

Interpréter une valeur manquante avant de la remplacer

Une ligne de jointure externe complétée par des NULL signifie qu’aucune ligne ne correspond. Un NULL stocké dans une commande réelle peut en revanche signifier qu’un champ n’a pas été renseigné. Ces situations sont différentes, même si toutes deux s’affichent sous la forme NULL.

COALESCE(o.total, 0) choisit le premier argument non NULL. Cette expression afficherait 0 pour Chloe et Dev. Cela peut être utile pour un rapport de dépenses, mais un zéro dans une colonne intitulée « total de la commande » peut suggérer une commande réelle d’un montant nul. Veille à ce que l’intitulé et le sens du rapport concordent.

Les lignes nécessaires se trouvent parfois à droite d’une jointure. Il faut parfois conserver les deux tables d’entrée pour comparer les enregistrements manquants. Les jointures externes droites et complètes rendent ces choix explicites.

Réécrire une jointure droite en jointure gauche

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

Cette requête conserve toutes les commandes : (Ana, 101), (Ana, 102), (Ben, 103) et (NULL, 104). La table de droite est orders, donc la commande passée sans compte est conservée. Chloe et Dev sont des lignes de gauche sans correspondance et ne sont pas conservés.

Permuter les tables d’entrée donne l’expression équivalente orders o LEFT JOIN customers c ON o.customer_id = c.customer_id. Conserver la même liste explicite de sélection donne les mêmes valeurs. Avec SELECT *, l’ordre par défaut des colonnes peut changer. Utiliser des jointures gauches dans tout un rapport facilite souvent le suivi du côté à conserver.

Conserver les lignes sans correspondance des deux tables

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;

Le résultat contient (Ana, 101), (Ana, 102), (Ben, 103), (Chloe, NULL), (Dev, NULL) et (NULL, 104). Il s’agit de trois paires correspondantes, de deux clients sans correspondance et d’une commande sans correspondance : six lignes. Les colonnes du client sont NULL pour la commande 104 passée sans compte ; les colonnes de commande sont NULL pour Chloe et Dev.

Une jointure complète est utile pour le rapprochement, par exemple pour comparer une liste de stocks d’entrepôt à un inventaire physique. Les clés correspondantes apparaissent ensemble ; les clés présentes d’un seul côté restent visibles. Dans la requête du magasin, ajouter WHERE c.customer_id IS NULL OR o.order_id IS NULL ne conserve que Chloe, Dev et la commande 104.

Lors du rapprochement de deux listes comportant le même type de clé, l’une ou l’autre clé d’entrée peut être NULL dans le résultat. COALESCE(w.sku, k.sku) AS sku sélectionne la référence SKU disponible depuis l’alias d’entrepôt w ou l’alias de liste d’inventaire k. Une colonne de clé combinée permet au rapport d’identifier les enregistrements absents de l’une ou l’autre liste.

Vérifier la prise en charge de la syntaxe

PostgreSQL, SQL Server et Oracle prennent en charge les jointures externes droites et complètes. SQLite prend en charge les deux à partir de la version 3.39.0, publiée en 2022. Les versions antérieures de SQLite les refusent. MySQL prend en charge les jointures droites mais ne possède pas FULL OUTER JOIN ; une solution consiste à combiner les résultats de jointures gauche et droite avec UNION.

Les anciennes requêtes SQL d’Oracle peuvent à la place marquer le côté facultatif avec (+) dans WHERE. Par exemple, WHERE c.customer_id = o.customer_id(+) conserve les clients et remplit les colonnes des commandes manquantes avec NULL, comme une jointure gauche partant des clients. Le marqueur appartient au côté qui peut être absent. Utilise les formes de jointure LEFT, RIGHT et FULL lorsque tu écris de nouvelles requêtes.

Une jointure externe distingue deux décisions : quelles paires comptent comme correspondances et quelles lignes du résultat construit le rapport conserve. ON prend la première décision. WHERE prend la seconde, après que les lignes sans correspondance ont reçu des NULL.

Conserver tout le monde, mais limiter les commandes correspondantes

Supposons qu’un rapport demande tous les clients et uniquement leurs commandes à partir du 10 mars :

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.order_date >= '2026-03-10';

Le test de date fait partie de la recherche de correspondances. La commande 101 d’Ana est trop ancienne, mais la commande 102 satisfait la condition. La commande 103 de Ben la satisfait aussi. Chloe et Dev n’ont aucune correspondance satisfaisant la condition : chacun reçoit donc une ligne complétée par des NULL. Le résultat est (Ana, 102), (Ben, 103), (Chloe, NULL) et (Dev, NULL).

Filtrer les lignes après la jointure

Déplace le même test de date dans WHERE :

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-03-10';

La jointure crée maintenant d’abord cinq lignes, y compris les lignes complétées par des NULL pour Chloe et Dev. La comparaison de date est inconnue pour ces valeurs NULL : WHERE les supprime donc. Il supprime aussi la commande ancienne d’Ana. Seuls (Ana, 102) et (Ben, 103) restent.

La version avec WHERE produit les mêmes lignes correspondantes qu’une jointure interne avec ce filtre de date.

Filtrer volontairement la table conservée

Une condition sur le côté gauche dans ON ne peut pas non plus supprimer les lignes de gauche. Considère ON o.customer_id = c.customer_id AND c.city = 'Denver'. Ana peut correspondre aux deux commandes, Chloe n’en a aucune, et Ben et Dev ne satisfont pas la partie sur la ville dans toutes les correspondances possibles. Les quatre clients restent, avec des colonnes de commande NULL pour Ben, Chloe et Dev.

Pour demander uniquement les clients de Denver, place WHERE c.city = 'Denver' après la condition de clé habituelle. Le résultat est (Ana, 101), (Ana, 102) et (Chloe, NULL). Chloe est conservée parce que le filtre teste sa propre ville, et non un champ de commande manquant.

Le test d’anti-jointure se place après la jointure parce qu’il examine le résultat complété par des NULL. Les jointures internes ne conservent aucune ligne sans correspondance : déplacer un filtre entre ON et WHERE ne change donc pas leurs paires correspondantes. Lorsque tu vérifies une jointure externe, lis chaque test WHERE sur le côté facultatif et examine son effet sur une ligne complétée par des NULL.

Le responsable d’un employé est lui aussi un employé. Une auto-jointure lit la même table sous différents alias afin qu’une ligne puisse jouer le rôle de l’employé et une autre celui du responsable. Elle utilise la syntaxe habituelle des jointures ; il n’existe pas de mot-clé SELF JOIN.

Nommer les rôles avant les colonnes

Voici les données des employés :

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id;

e fournit la ligne de l’employé. Son manager_id pointe vers le emp_id du responsable dans le rôle m. Hassan pointe vers 1 : il est donc associé à Grace ; Iris est aussi associée à Grace. Jamal pointe vers 2 et est associé à Hassan. Le résultat est (Hassan, Grace), (Iris, Grace) et (Jamal, Hassan).

Grace n’a pas de responsable. Son manager_id est NULL, donc la jointure interne l’exclut. Remplacer JOIN par LEFT JOIN ajoute (Grace, NULL), ce qui produit quatre lignes. Des alias distincts permettent à la base de données de différencier les rôles même si les deux entrées proviennent de la même table stockée.

Poser la question dans l’autre sens

Pour trouver les personnes qui ne dirigent personne, pars du rôle de responsable et cherche un employé manquant :

SELECT m.name
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.emp_id
WHERE e.emp_id IS NULL;

La requête renvoie (Iris) et (Jamal). Grace correspond à Hassan et Iris ; Hassan correspond à Jamal. Iris et Jamal n’ont aucun subordonné direct. Tester la clé de l’employé après la jointure permet de trouver leur correspondance manquante.

Un troisième alias permet de suivre un niveau supplémentaire. employees e JOIN employees m ON e.manager_id = m.emp_id JOIN employees g ON m.manager_id = g.emp_id, en sélectionnant leurs noms, renvoie (Jamal, Hassan, Grace). Chaque rôle supplémentaire suit un lien de plus. Une requête fixe atteint un nombre fixe de niveaux ; une hiérarchie de profondeur quelconque nécessite une requête récursive.

Former les paires de collègues une seule fois

La même table peut aussi fournir des paires de collègues :

SELECT a.name, b.name
FROM employees a
JOIN employees b
  ON a.manager_id = b.manager_id
 AND a.emp_id < b.emp_id;

Hassan et Iris ont le même responsable, le numéro 1. Le test « inférieur à » exclut les paires d’une personne avec elle-même et ne conserve qu’un seul ordre pour chaque paire. Le résultat est (Hassan, Iris).

Avec la seule égalité sur le responsable, le résultat comporte cinq lignes : Hassan–Hassan, Hassan–Iris, Iris–Hassan, Iris–Iris et Jamal–Jamal. La clé de responsable NULL de Grace ne correspond à rien, pas même à un autre NULL. Remplacer < par <> exclut les paires réflexives mais conserve (Hassan, Iris) et (Iris, Hassan). Choisis < lorsque la question demande chaque paire non ordonnée une seule fois.

Une jointure croisée construit toutes les paires de lignes, sans condition de correspondance. Elle est utile lorsque la question porte sur les possibilités, comme toutes les combinaisons de tailles et de couleurs qu’un magasin pourrait stocker.

Construire la liste des combinaisons

sizes contient S, M et L. colors contient Red et Blue.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Les six paires sont (S, Red), (S, Blue), (M, Red), (M, Blue), (L, Red) et (L, Blue). Chaque taille apparaît à côté des deux couleurs. Ce sont les lignes du résultat ; leur ordre d’affichage n’est pas garanti sans ORDER BY.

Pour deux tables d’entrée, multiplie leurs nombres de lignes. Trois tailles et deux couleurs donnent six lignes. Avec une troisième table contenant quatre options, le résultat comporte 24 lignes. Si l’une des tables d’entrée est vide, aucune combinaison complète ne peut être formée et le résultat comporte zéro ligne.

Partir d’une grille de rapport complète

Les enregistrements de ventes ne montrent que les combinaisons qui se sont produites. Pour afficher chaque magasin chaque mois, forme d’abord une grille avec stores CROSS JOIN months. Utilise ensuite une jointure gauche pour rattacher les enregistrements de ventes correspondants :

SELECT s.store_id, m.month, x.total
FROM stores s
CROSS JOIN months m
LEFT JOIN sales x
  ON x.store_id = s.store_id
 AND x.month = m.month;

La grille conserve une paire magasin-mois même en l’absence de vente, avec NULL dans x.total. Les deux parties de la clé comptent : une correspondance sur le seul magasin rattacherait aussi ses ventes des autres mois. La jointure croisée définit les combinaisons que le rapport doit afficher, et la jointure gauche fournit les informations disponibles à leur sujet.

Repérer une jointure croisée involontaire

FROM customers, orders sans condition de jointure forme 16 paires à partir des quatre clients et des quatre commandes. Ana est associée à ses propres commandes, à celle de Ben et à la commande passée sans compte. Il en va de même pour chaque client. Cela n’est utile que si toutes les combinaisons client-commande sont réellement voulues.

Un grand nombre de lignes est un indice, mais examine aussi les associations. Avec 1 000 clients et 1 000 produits, une jointure croisée volontaire produit 1 000 000 lignes. Une jointure dont la condition est incorrecte mais sélective peut aussi renvoyer un résultat plus petit et incorrect. Compare le nombre et le sens de chaque paire à la question posée.

Reconnaître les formes équivalentes

CROSS JOIN, une jointure par virgule sans condition de correspondance et une jointure interne avec ON 1 = 1 conservent toutes les paires. SQLite et MySQL acceptent aussi un simple JOIN sans ON et le traitent comme une jointure croisée ; PostgreSQL exige ON ou USING pour une jointure simple. Écris CROSS JOIN lorsque toutes les combinaisons sont voulues.

DISTINCT supprime les lignes sélectionnées identiques, pas les associations incorrectes. Si une jointure croisée accidentelle associe Ana à la commande de Ben, ces valeurs restent une ligne distincte. Corrige la condition manquante au lieu de masquer une partie des répétitions du résultat.

Un étudiant peut suivre plusieurs cours et un cours peut accueillir plusieurs étudiants. Une table d’association enregistre ces liens plusieurs-à-plusieurs sous forme de paires individuelles. Elle est aussi appelée table de liaison.

Lire un lien par ligne

Les données d’exemple contiennent les étudiants 1 Mia, 2 Noah et 3 Omar ; les cours 10 Algebra, 20 Biology et 30 Chemistry ; et les paires d’inscription (1, 10), (1, 20) et (2, 10).

L’étudiante 1 apparaît deux fois parce que Mia suit deux cours. Le cours 10 apparaît deux fois parce qu’Algebra accueille deux étudiants. Aucune des deux colonnes ne doit être unique à elle seule. La paire peut former une clé primaire composite, PRIMARY KEY (student_id, course_id), qui empêche d’inscrire deux fois le même étudiant au même cours.

Un lien peut avoir ses propres informations. enrolled_on appartient à l’inscription parce que cette colonne décrit quand un étudiant donné a rejoint un cours donné. De même, order_items.quantity décrit la quantité d’un produit donné dans une commande donnée.

Passer par la table d’association avec deux jointures

SELECT s.name, c.title
FROM students s
JOIN enrollments e ON e.student_id = s.student_id
JOIN courses c ON c.course_id = e.course_id;

La première condition rattache les lignes d’inscription de chaque étudiant. La seconde remplace la référence au cours de chaque inscription par des informations de la table des cours. Le résultat complet est (Mia, Algebra), (Mia, Biology) et (Noah, Algebra). Chaque ligne représente une inscription.

Aucune des deux tables situées aux extrémités ne contient de clé reliant directement un étudiant à un cours. Une correspondance sur s.student_id = c.course_id comparerait des identifiants sans lien et ignorerait les informations d’inscription. La table d’association fournit le lien.

Le même chemin fonctionne dans les deux sens. Ajoute WHERE c.title = 'Algebra' pour obtenir (Mia, Algebra) et (Noah, Algebra). Ajoute plutôt WHERE s.name = 'Mia' pour obtenir (Mia, Algebra) et (Mia, Biology). Les liens restent les mêmes ; le filtre sélectionne ceux qui répondent à la question.

Inclure les personnes ou les cours sans lien

Pars du côté qui doit être conservé. students s LEFT JOIN enrollments e ON e.student_id = s.student_id conserve Omar avec des colonnes d’inscription NULL. Pour afficher les titres des cours tout en le conservant, la jointure ultérieure entre les inscriptions et les cours doit aussi préserver l’inscription manquante.

Pour trouver les cours que personne ne suit, pars du côté des cours et utilise une anti-jointure :

SELECT c.title
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
WHERE e.student_id IS NULL;

La requête renvoie (Chemistry). Dans une inscription réellement correspondante, student_id fait partie de la clé primaire et ne peut pas être NULL : le test identifie donc le lien manquant.

Stocke une association par ligne de la table d’association. Une liste textuelle comme « 10, 20 » dans un champ d’étudiant ne peut pas être comparée par égalité aux identifiants individuels de cours 10 et 20. Des paires distinctes permettent à chaque condition de jointure de comparer une référence à une clé.

Une jointure plus longue suit la même règle qu’une jointure à deux tables : rattacher la table suivante à l’aide d’une condition pertinente. Avant d’écrire la requête, trace le chemin des clés et décide ce que doit représenter une ligne du résultat.

Ajouter les données détaillées du magasin

Les lignes de commande (order_id, product_id, quantity) sont (101, 1, 2), (101, 2, 4), (102, 3, 6), (103, 1, 6), (103, 2, 8) et (104, 2, 10). Chaque ligne relie une commande à un produit et enregistre une quantité.

Pour répondre à « Qu’a acheté chaque client ? », suis customers → orders → order_items → products. Les clés du chemin sont l’identifiant client, l’identifiant de commande et l’identifiant de produit. Les quatre tables de cette chaîne nécessitent trois liens.

Rattacher une nouvelle table à la fois

SELECT c.name, o.order_id, p.name AS product,
       oi.quantity, oi.quantity * p.price AS line_total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_id;

Chaque ON fait référence à la table nouvellement introduite et à une table déjà disponible. Les produits sont rattachés par les lignes de commande, et non directement par les clients. Voici le résultat :

La première jointure exclut la commande 104 passée sans compte parce que sa référence client est NULL. Les trois commandes restantes contiennent cinq lignes : le résultat final comporte donc cinq lignes. Pour la commande 101, deux mugs coûtent 16,00 et quatre stylos coûtent 6,00, ce qui correspond au total de commande de 22,00.

Savoir ce que représente une ligne

Une ligne du résultat représente une ligne de commande. Ana et la commande 101 apparaissent deux fois parce que cette commande contient deux produits. La répétition des noms de clients est donc attendue. Compter ces lignes revient à compter des lignes de commande, et non des clients ou des commandes distincts.

Un filtre peut faire référence à n’importe quelle table jointe. Ajouter WHERE p.name = 'Pen' avant ORDER BY conserve (Ana, 101, Pen, 4, 6.00) et (Ben, 103, Pen, 8, 12.00). Aucun filtre de produit ne peut rétablir la commande passée sans compte, déjà exclue au niveau du lien avec les clients.

Garder chaque lien visible

Pour une chaîne de jointures internes, l’ordre d’écriture des tables peut changer sans modifier les combinaisons correspondantes, à condition que chaque condition ne fasse référence qu’aux tables déjà introduites. Partir du sujet du rapport et suivre ses clés est généralement plus facile à lire.

Une condition manquante peut multiplier un résultat intermédiaire dans SQLite ou MySQL, qui acceptent un simple JOIN sans ON. PostgreSQL et SQL Server refusent cette syntaxe. Une condition manquante dans une requête par virgule peut aussi créer toutes les combinaisons à cette étape.

Une table joue parfois deux rôles. Un rapport sur les vols peut joindre airports sous l’alias dep avec dep.code = f.from_code, puis sous l’alias arr avec arr.code = f.to_code. Des alias distincts donnent aux informations de départ et d’arrivée des places séparées dans le résultat.

Une jointure gauche conserve des lignes à une étape. Les jointures et les filtres ultérieurs agissent toujours sur le résultat de cette étape. Pour conserver un parent sans correspondance le long d’un chemin plus long, suis ce qui arrive à ses colonnes complétées par des NULL à chaque étape.

Voir où une ligne conservée disparaît

SELECT c.name, o.order_id, oi.product_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id;

La première étape produit cinq lignes : les deux commandes d’Ana, la commande de Ben et une ligne complétée par des NULL pour chacun des deux clients Chloe et Dev. À l’étape suivante, les valeurs o.order_id de Chloe et Dev sont NULL. Elles ne peuvent pas satisfaire l’égalité avec une clé de ligne de commande : la jointure interne les supprime donc. La requête renvoie cinq lignes de commande et perd les deux clients sans commande.

Préserver le chemin facultatif

SELECT c.name, o.order_id, oi.product_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.order_id;

La seconde jointure conserve maintenant aussi ces lignes complétées par des NULL. Le résultat contient (Ana, 101, 1), (Ana, 101, 2), (Ana, 102, 3), (Ben, 103, 1), (Ben, 103, 2), (Chloe, NULL, NULL) et (Dev, NULL, NULL) : sept lignes.

Lorsque des jointures ultérieures dépendent des colonnes d’une table facultative, elles doivent généralement rester des jointures gauches pour conserver les lignes sans correspondance. Une jointure interne ultérieure portant sur les colonnes d’une table obligatoire joue un rôle différent : examine donc la dépendance réelle de chaque condition.

Suivre la même règle par une table d’association

SELECT s.name, c.title
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id
LEFT JOIN courses c ON c.course_id = e.course_id;

Cette requête renvoie (Mia, Algebra), (Mia, Biology), (Noah, Algebra) et (Omar, NULL). Transformer la dernière jointure en jointure interne supprime Omar, car son inscription manquante ne peut pas correspondre à un cours.

Les parenthèses offrent une autre façon d’exprimer le regroupement : students s LEFT JOIN (enrollments e JOIN courses c ON c.course_id = e.course_id) ON e.student_id = s.student_id. Les correspondances entre inscriptions et cours forment le groupe facultatif ; la jointure externe rattache ce groupe à chaque étudiant et conserve Omar.

Vérifier les filtres ultérieurs et le sens de la jointure

Ajouter WHERE c.title = 'Algebra' à la requête sur les étudiants qui n’utilise que des jointures gauches ne laisse que Mia et Noah en Algebra. La comparaison est inconnue pour le titre de cours NULL d’Omar. Cela répond à « Qui suit Algebra ? », mais ne répond plus à « Afficher tous les étudiants et leurs cours ». Un test IS NULL se comporte différemment parce qu’il accepte volontairement une valeur manquante.

Une jointure droite ultérieure change de nouveau le côté conservé : elle préserve la nouvelle table de droite et les lignes précédentes sans correspondance peuvent disparaître. Place la table à conserver en premier et rattache les chemins facultatifs avec des jointures gauches lorsque cela exprime clairement le rapport.

Construis et examine la requête une jointure à la fois. Note à la fois le nombre de lignes et les clés qui restent. Une baisse peut révéler la perte de lignes sans correspondance ; une hausse inattendue peut révéler une partie de clé manquante. Certains nombres restent inchangés lorsque des lignes sont à la fois perdues et multipliées : examine donc aussi des clés représentatives.

Une jointure peut être correcte alors qu’un agrégat calculé sur son résultat est faux. La question essentielle est de savoir ce que représente une ligne après la jointure. Une valeur stockée une fois par commande peut apparaître plusieurs fois dans une liste de lignes de commande.

Suivre un total de commande répété

Ana a la commande 101 d’un montant de 22,00 et la commande 102 d’un montant de 25,50. Additionner les deux commandes donne 47,50. La commande 101 comporte deux lignes : la jointure avec order_items répète donc deux fois 22,00 ; la commande 102 comporte une ligne. Additionner les valeurs répétées donne 69,50.

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

GROUP BY rassemble les lignes de chaque client et SUM additionne les valeurs de ce groupe. Cette requête renvoie (1, Ana, 47.50) et (2, Ben, 60.00). Ajouter la jointure avec les lignes de commande avant le regroupement gonflerait le total d’Ana à 69,50 et celui de Ben à 120,00. Les totaux des deux clients passeraient à 189,50 au lieu de 107,50.

Sur les quatre commandes, y compris la commande 104 passée sans compte, joindre directement les commandes à leurs lignes amène SUM(o.total) à renvoyer 204,50 au lieu de 122,50. Cette démultiplication provient du lien un-à-plusieurs, et non d’une erreur de syntaxe.

Additionner les valeurs au niveau où elles sont stockées

Pour les totaux de commandes, additionne les commandes sans ajouter la table de détail. Pour obtenir un total des montants de lignes, calcule la valeur propre à chaque ligne :

SELECT SUM(oi.quantity * p.price) AS sales
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id;

Les six montants de lignes sont 16,00, 6,00, 25,50, 48,00, 12,00 et 15,00. Leur somme est de 122,50, le total des quatre commandes. Chaque produit de la quantité par le prix appartient à une ligne et n’est donc ajouté qu’une fois.

Compter les correspondances réelles

Après avoir joint les clients aux commandes et aux lignes, Ana occupe trois lignes. COUNT(*) compte ces lignes ; COUNT(DISTINCT o.order_id) compte ses deux commandes distinctes.

Après une simple jointure gauche des clients aux commandes, COUNT(*) compte une ligne complétée par des NULL pour Chloe et une pour Dev. Compte plutôt la clé de commande facultative :

SELECT c.customer_id, c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

La requête renvoie (1, Ana, 2), (2, Ben, 1), (3, Chloe, 0) et (4, Dev, 0). COUNT(column) ignore les valeurs NULL ; COUNT(*) compte les lignes.

Deux côtés plusieurs se multiplient entre eux

Si un client a trois commandes et deux tickets, joindre indépendamment les deux tables enfants à ce client produit six lignes. Chaque commande est associée à chaque ticket. Un simple comptage de l’une ou l’autre clé enfant donne six. Compte les clés enfants distinctes pour obtenir trois et deux, ou calcule séparément le total de chaque table enfant.

DISTINCT c.customer_id, c.name est utile pour obtenir une liste de clients uniques. Garder la clé évite de fusionner des clients différents qui portent le même nom. Cela ne corrige pas une condition de jointure manquante. SUM(DISTINCT o.total) n’est pas sûr non plus : cette expression fusionne deux commandes distinctes qui ont par hasard le même montant.

Une requête peut s’exécuter tout en décrivant de mauvaises associations. Avant d’utiliser le résultat d’une jointure, prévois ce qui doit être conservé, combien de fois chaque clé doit apparaître et ce que doit représenter une ligne du résultat.

Prévoir le nombre de lignes

Si la référence client d’une commande correspond à une clé client unique, chaque commande a au plus un client correspondant. Une jointure interne renvoie donc au plus une ligne par commande. Dans les données du magasin, trois commandes sur quatre ont un client : la jointure interne entre clients et commandes renvoie donc trois lignes.

En partant des clients, une jointure gauche renvoie les trois paires correspondantes plus une ligne pour Chloe et une pour Dev : cinq lignes. Une jointure croisée des quatre clients et des quatre commandes donne 16 lignes. Ces prévisions permettent des vérifications différentes selon le type de jointure ; le seul nombre de tables ne détermine pas le nombre de lignes.

Examiner la forme d’un résultat incorrect

Une correspondance sur store_id seul lorsque les enregistrements sont identifiés par (store_id, month) mélange différents mois. Fais correspondre chaque composante avec AND. Une requête qui fonctionne par hasard tant qu’un magasin n’a qu’un mois peut échouer dès qu’un second mois est chargé.

Distinguer les valeurs manquantes des clés incorrectes

Avec une égalité ordinaire, les clés NULL ne correspondent pas, y compris lorsque NULL est comparé à NULL. Joindre les commandes à elles-mêmes sur customer_id donne quatre paires issues des deux commandes d’Ana et une issue de la commande de Ben, soit cinq lignes. La commande passée sans compte n’en produit aucune. Une jointure externe est nécessaire lorsqu’une ligne dont la référence est manquante doit être conservée.

Des valeurs de recherche en double ne multiplient que les correspondances de ces valeurs. Si un code pays apparaît deux fois dans une table de recherche, chaque client portant ce code obtient deux lignes correspondantes. Les codes présents une seule fois donnent toujours une seule correspondance. Examine une table de recherche censée être unique avec :

SELECT code, COUNT(*) AS copies
FROM countries
GROUP BY code
HAVING COUNT(*) > 1;

GROUP BY rassemble les lignes de chaque code ; HAVING conserve les groupes dont le nombre dépasse un. Charger deux fois toute la table de recherche doublerait les lignes de chaque client correspondant.

Examine aussi les types et les formats des clés. Le texte '0042' et le nombre 42 peuvent se comporter différemment selon la base de données : absence de correspondance, erreur de type ou conversion automatique. SQLite peut faire correspondre une colonne TEXT contenant '0042' à une colonne INTEGER contenant 42. Utilise des types et des formats de clés cohérents, ou une conversion explicite choisie selon le sens voulu.

Examiner les exigences du rapport

Demande-toi quelle table doit conserver toutes ses lignes. Trace son chemin complet de clés et vérifie chaque condition par rapport au schéma. Détermine pourquoi chaque lien est interne ou externe, puis vérifie si chaque filtre doit s’appliquer pendant ou après la recherche de correspondances. Compare les nombres de lignes après chaque étape et examine une ligne correspondante connue, une ligne sans correspondance et une ligne ayant plusieurs correspondances. Vérifie enfin que chaque comptage et chaque somme portent sur le type d’enregistrement voulu.