Les jointures

Le problème : le nom du client n'est pas dans la commande

Vous voulez afficher « Marie Dupont a commandé pour 89,90 € ». Mais la table commandes ne contient pas le nom de Marie. Elle contient seulement un client_id = 1. Le nom, lui, est dans la table clients.

C'est volontaire. Si on stockait « Marie Dupont » dans chaque commande, et qu'elle se marie et change de nom, il faudrait modifier des milliers de lignes. En séparant, on écrit le nom une seule fois. C'est le principe des relations. Pour rassembler les morceaux au moment de l'affichage, on utilise une jointure.

clients                      commandes
id | nom            | ville   id | client_id | montant
---+----------------+------   ---+-----------+--------
1  | Marie Dupont   | Besac.  1  | 1         | 89.90
2  | Karim Benali   | Dijon   2  | 2         | 42.00
3  | Léa Martin     | Lyon    3  | 1         | 120.50

La clé étrangère : le pont entre deux tables

Dans commandes, la colonne client_id est une clé étrangère (foreign key) : elle pointe vers la clé primaire id de la table clients. C'est le fil qui relie une commande à son client.

INNER JOIN : ne garder que les correspondances

Un INNER JOIN assemble deux tables sur une condition de correspondance définie par ON. Il ne garde que les lignes qui matchent des deux côtés.

SELECT c.nom, cmd.montant
FROM clients AS c
INNER JOIN commandes AS cmd
    ON cmd.client_id = c.id
ORDER BY c.nom;

On lit : « pour chaque commande, va chercher dans clients la ligne dont l'id égale le client_id, et colle les colonnes ensemble ». Marie apparaît deux fois (elle a deux commandes), Léa n'apparaît pas (elle n'a aucune commande).

LEFT JOIN : garder tout le monde, même sans correspondance

Et si on veut tous les clients, y compris ceux qui n'ont jamais commandé ? L'INNER JOIN les oublie. Le LEFT JOIN garde toutes les lignes de la table de gauche (clients), et met NULL dans les colonnes de droite quand il n'y a pas de correspondance.

SELECT c.nom, cmd.montant
FROM clients AS c
LEFT JOIN commandes AS cmd
    ON cmd.client_id = c.id
ORDER BY c.nom;

Cette fois, Léa apparaît avec un montant à NULL. On combine alors ce LEFT JOIN avec l'agrégation déjà vue en leçon 4 (SUM + GROUP BY) pour répondre à « combien chaque client a-t-il dépensé, zéro inclus ». La seule vraie nouveauté ici, c'est COALESCE :

SELECT c.nom, COALESCE(SUM(cmd.montant), 0) AS total
FROM clients AS c
LEFT JOIN commandes AS cmd
    ON cmd.client_id = c.id
GROUP BY c.id, c.nom
ORDER BY total DESC;

Les sous-requêtes : une requête dans une requête

Une sous-requête est un SELECT imbriqué entre parenthèses à l'intérieur d'une autre requête. Le moteur SQL exécute d'abord la sous-requête, puis utilise le résultat pour compléter la requête extérieure.

Cas 1 : sous-requête scalaire. Elle renvoie une seule valeur et s'utilise dans un WHERE avec une comparaison classique. Par exemple, « les produits plus chers que la moyenne » :

SELECT nom, prix
FROM produits
WHERE prix > (SELECT AVG(prix) FROM produits);

La sous-requête (SELECT AVG(prix) FROM produits) est évaluée en premier et renvoie un nombre (la moyenne). Ce nombre sert ensuite à filtrer la requête principale. Exécutez ci-dessous pour le vérifier :

CREATE TABLE produits (id INTEGER, nom TEXT, prix REAL);
INSERT INTO produits VALUES (1, 'Stylo', 1.50);
INSERT INTO produits VALUES (2, 'Cahier', 3.20);
INSERT INTO produits VALUES (3, 'Cartable', 28.00);
INSERT INTO produits VALUES (4, 'Règle', 0.90);

SELECT nom, prix
FROM produits
WHERE prix > (SELECT AVG(prix) FROM produits);

Cas 2 : sous-requête liste avec IN. Elle renvoie plusieurs valeurs et filtre les lignes dont une colonne appartient à cette liste. Par exemple, « les clients qui ont déjà commandé » :

SELECT nom
FROM clients
WHERE id IN (SELECT client_id FROM commandes);

La sous-requête (SELECT client_id FROM commandes) produit la liste de tous les identifiants clients présents dans les commandes. La requête principale ne garde que les clients dont l'id figure dans cette liste :

CREATE TABLE clients (id INTEGER, nom TEXT);
CREATE TABLE commandes (id INTEGER, client_id INTEGER, montant REAL);
INSERT INTO clients VALUES (1, 'Alice Durand');
INSERT INTO clients VALUES (2, 'Marc Petit');
INSERT INTO clients VALUES (3, 'Léa Martin');
INSERT INTO commandes VALUES (1, 1, 120.50);
INSERT INTO commandes VALUES (2, 2, 45.00);

SELECT nom
FROM clients
WHERE id IN (SELECT client_id FROM commandes);

Sous-requête ou jointure ? Les jointures (vues dans les sections précédentes) combinent les colonnes de deux tables pour afficher des données issues des deux. Les sous-requêtes servent plutôt à filtrer selon une valeur ou une liste calculée par une autre requête. Les deux se recouvrent parfois : le WHERE id IN (SELECT client_id FROM commandes) peut souvent se réécrire en INNER JOIN. La sous-requête est souvent plus lisible quand l'objectif est un simple filtre et qu'on n'a pas besoin d'afficher des colonnes de la seconde table.