Jointures SQL : INNER JOIN et LEFT JOIN

Une jointure SQL combine les lignes de deux tables en suivant une clé étrangère : INNER JOIN garde les lignes qui ont une correspondance des deux côtés, LEFT JOIN garde aussi celles de la table de gauche qui n'en ont pas. Ce chapitre couvre aussi les jointures sur trois tables, l'auto-jointure et la méthode pour passer d'une question à une requête. Les exemples suivent le schéma de la base d'exemple parc.

Pourquoi joindre

La base range chaque information une seule fois : le nom d'un service est dans service, le nom d'un employé dans employe, et la clé étrangère employe.id_service relie les deux. Pour afficher « Julie Lefèvre, Comptabilité », tu dois donc recoller les deux tables en suivant cette clé. C'est une jointure. Toute jointure correcte suit une clé étrangère du schéma : si tu ne sais pas sur quelle colonne joindre, retourne au schéma de la base.

Le produit cartésien

Sans condition, combiner deux tables associe chaque ligne de la première à chaque ligne de la seconde :

SELECT COUNT(*) FROM service, employe;

6 services × 14 employés = 84 lignes, dont 70 n'ont aucun sens (Julie Lefèvre associée à la Direction, au Commercial, etc.). Ce produit cartésien s'écrit aussi service CROSS JOIN employe. Il a de rares usages volontaires, comme générer toutes les combinaisons possibles (chaque poste avec chaque logiciel obligatoire) pour ensuite chercher celles qui manquent.

INNER JOIN

La jointure interne garde les combinaisons qui vérifient la condition ON :

SELECT e.nom, e.prenom, s.libelle
FROM employe e
INNER JOIN service s ON s.id_service = e.id_service
ORDER BY s.libelle, e.nom;

INNER est facultatif : JOIN seul est une jointure interne. Les lettres e et s sont des alias de table : ils raccourcissent la requête et lèvent les ambiguïtés. Les deux tables ont une colonne id_service ; si tu écris SELECT id_service sans préfixe, MySQL ne sait pas laquelle choisir :

ERROR 1052 (23000): Column 'id_service' in field list is ambiguous

Le service Juridique n'apparaît pas : aucun employé ne le référence, donc aucune combinaison ne vérifie la condition. C'est la règle de la jointure interne : une ligne sans correspondance disparaît.

Jointure sur trois tables

Pour aller d'une table à une autre qui n'est pas directement reliée, tu traverses les tables intermédiaires en suivant les clés, même si tu n'affiches aucune de leurs colonnes. Qui utilise le logiciel Sage ? Le chemin dans le schéma : logiciel → installation → poste → employe.

SELECT e.nom, e.prenom
FROM logiciel l
JOIN installation i ON i.id_logiciel = l.id_logiciel
JOIN poste p        ON p.id_poste    = i.id_poste
JOIN employe e      ON e.id_employe  = p.id_employe
WHERE l.nom = 'Sage 100 Comptabilité'
ORDER BY e.nom;

Chaque JOIN ajoute une table et sa condition. Écris-les dans l'ordre du chemin : chaque condition relie la nouvelle table à une table déjà présente. Autre exemple, les tickets non résolus avec le nom du demandeur et son service :

SELECT t.id_ticket, t.titre,
       CONCAT(e.prenom, ' ', e.nom) AS demandeur,
       s.libelle AS service
FROM ticket t
JOIN employe e ON e.id_employe = t.id_demandeur
JOIN service s ON s.id_service = e.id_service
WHERE t.statut <> 'resolu'
ORDER BY t.id_ticket;

LEFT JOIN et RIGHT JOIN

La jointure externe gauche garde toutes les lignes de la table de gauche (celle écrite avant LEFT JOIN). Quand une ligne n'a pas de correspondance, les colonnes de la table de droite valent NULL.

SELECT s.libelle, e.nom
FROM service s
LEFT JOIN employe e ON e.id_service = s.id_service
WHERE s.id_service IN (5, 6)
ORDER BY s.libelle, e.nom;

RIGHT JOIN garde toutes les lignes de la table de droite. A RIGHT JOIN B donne le même résultat que B LEFT JOIN A : par convention, on écrit presque toujours des LEFT JOIN, en plaçant à gauche la table dont on veut toutes les lignes.

Compter avec une jointure externe demande une précaution :

SELECT s.libelle,
       COUNT(e.id_employe) AS effectif,
       COUNT(*)            AS faux_effectif
FROM service s
LEFT JOIN employe e ON e.id_service = s.id_service
GROUP BY s.id_service, s.libelle
ORDER BY s.id_service;

La ligne Juridique existe, avec NULL partout du côté employe. COUNT(*) compte cette ligne ; COUNT(e.id_employe) ignore le NULL et donne bien 0.

ON ou WHERE dans une jointure externe

On veut tous les employés, avec leur portable s'ils en ont un. Première tentative :

SELECT e.nom, p.num_inventaire
FROM employe e
LEFT JOIN poste p ON p.id_employe = e.id_employe
WHERE p.type_poste = 'portable';

Cette requête ne renvoie que 7 employés. Pour ceux qui n'ont pas de portable, p.type_poste vaut NULL ou 'fixe', et le WHERE les élimine : le LEFT JOIN est devenu un INNER JOIN. La condition doit aller dans le ON, pour restreindre les correspondances sans éliminer les lignes de gauche :

SELECT e.nom, p.num_inventaire
FROM employe e
LEFT JOIN poste p
  ON p.id_employe = e.id_employe AND p.type_poste = 'portable'
ORDER BY e.nom, p.num_inventaire;

Elle renvoie les 14 employés, avec NULL pour les 7 qui n'ont pas de portable.

Trouver les lignes sans correspondance

La question « quels employés n'ont aucun poste ? » se traduit par une anti-jointure : une jointure externe, puis on garde les lignes où la table de droite n'a rien trouvé.

SELECT e.nom, e.prenom
FROM employe e
LEFT JOIN poste p ON p.id_employe = e.id_employe
WHERE p.id_poste IS NULL
ORDER BY e.nom;

Teste une colonne de la table de droite qui ne peut pas être NULL quand la correspondance existe : sa clé primaire est le choix sûr. Même schéma pour les logiciels jamais installés (Acrobat Pro) ou les tickets sans intervention (7, 12 et 14). Le chapitre Sous-requêtes SQL écrit la même chose avec NOT EXISTS.

L'auto-jointure

Une auto-jointure joint une table à elle-même. Elle sert dès qu'une clé étrangère pointe vers sa propre table, comme employe.id_responsable. On utilise deux alias, un par rôle :

SELECT e.nom AS employe, r.nom AS responsable
FROM employe e
LEFT JOIN employe r ON r.id_employe = e.id_responsable
ORDER BY e.id_employe;

Le LEFT JOIN garde Hélène Garnier, la directrice, qui n'a pas de responsable. L'organigramme complet :

Joindre deux fois la même table

Une intervention fait intervenir deux employés : le demandeur du ticket et le technicien. La table employe apparaît donc deux fois, sous deux alias :

SELECT i.id_intervention, i.id_ticket,
       dem.nom  AS demandeur,
       tech.nom AS technicien,
       i.duree_min
FROM intervention i
JOIN ticket t     ON t.id_ticket     = i.id_ticket
JOIN employe dem  ON dem.id_employe  = t.id_demandeur
JOIN employe tech ON tech.id_employe = i.id_technicien
WHERE i.id_ticket IN (2, 9)
ORDER BY i.date_intervention;

Choisis des alias parlants (dem, tech) plutôt que e1 et e2 : à la relecture, tu sais quel rôle joue chaque copie.

Jointures et agrégats

Joindre une table à deux tables enfants indépendantes multiplie les lignes. Sofiane Benali a 2 postes et a ouvert 2 tickets :

SELECT e.nom,
       COUNT(p.id_poste)  AS nb_postes,
       COUNT(t.id_ticket) AS nb_tickets
FROM employe e
LEFT JOIN poste p  ON p.id_employe   = e.id_employe
LEFT JOIN ticket t ON t.id_demandeur = e.id_employe
WHERE e.id_service = 3
GROUP BY e.id_employe, e.nom
ORDER BY e.nom;

Chacun de ses 2 postes est combiné avec chacun de ses 2 tickets : 4 lignes, donc 4 et 4. COUNT(DISTINCT p.id_poste) et COUNT(DISTINCT t.id_ticket) rétablissent 2 et 2, mais DISTINCT ne sauve pas une somme : SUM(p.prix_achat) donnerait 3 296 € au lieu de 1 648 €. La solution robuste agrège chaque table enfant avant de joindre, dans des tables dérivées ou des CTE.

USING, NATURAL JOIN et l'ancienne syntaxe

Tu croiseras trois autres écritures. Sache les lire : USING s'emploie avec prudence, évite les deux autres.

-- USING : quand la colonne porte le même nom dans les deux tables
SELECT e.nom, s.libelle FROM employe e JOIN service s USING (id_service);

-- NATURAL JOIN : joint sur TOUTES les colonnes de même nom
SELECT nom, libelle FROM employe NATURAL JOIN service;

-- Ancienne syntaxe : tables séparées par des virgules, condition dans le WHERE
SELECT e.nom, s.libelle FROM employe e, service s WHERE s.id_service = e.id_service;

Les trois donnent le même résultat aujourd'hui. NATURAL JOIN est dangereux : si quelqu'un ajoute une colonne date_maj aux deux tables, la jointure portera aussi sur elle et renverra des résultats faux, sans erreur. L'ancienne syntaxe mélange conditions de jointure et filtres dans le WHERE : oublie une condition et tu obtiens un produit cartésien. Écris JOIN … ON.

FULL OUTER JOIN avec UNION

La jointure externe complète garde les lignes sans correspondance des deux côtés. MySQL et MariaDB ne la proposent pas ; on l'obtient en réunissant un LEFT JOIN et un RIGHT JOIN. Tous les employés et tous les postes, affectés ou non :

SELECT e.nom, p.num_inventaire
FROM employe e LEFT JOIN poste p ON p.id_employe = e.id_employe
UNION
SELECT e.nom, p.num_inventaire
FROM employe e RIGHT JOIN poste p ON p.id_employe = e.id_employe
ORDER BY nom, num_inventaire;

Le résultat compte 17 lignes : les 13 postes affectés, Inès Bonnet et Paul Martin avec NULL comme poste, et les postes PC-0013 et PC-0014 avec NULL comme nom (triés en premier, puisque MySQL place les NULL en tête). UNION supprime les lignes communes aux deux parties. PostgreSQL, SQL Server et SQLite écrivent directement FULL OUTER JOIN.

De la question à la jointure : la méthode

Prends une question d'examen : « Pour chaque service, le nombre de tickets non résolus ouverts par ses employés, y compris les services qui n'en ont aucun. »

1. Ce qu'on affiche et ce qu'on filtre : le libellé du service, un nombre de tickets ; un filtre sur le statut.
2. Les tables qui contiennent ces colonnes : service (libellé), ticket (statut, comptage).
3. Le chemin entre elles dans le schéma : service ← employe ← ticket. La table employe est indispensable, même si aucune de ses colonnes n'est affichée.
4. Interne ou externe : « y compris les services qui n'en ont aucun » impose LEFT JOIN depuis service. Et le filtre sur le statut, qui porte sur la table de droite, va dans le ON.
5. Écrire dans l'ordre : FROM et jointures, WHERE, GROUP BY, SELECT, ORDER BY.

SELECT s.libelle, COUNT(t.id_ticket) AS tickets_non_resolus
FROM service s
LEFT JOIN employe e ON e.id_service = s.id_service
LEFT JOIN ticket t  ON t.id_demandeur = e.id_employe AND t.statut <> 'resolu'
GROUP BY s.id_service, s.libelle
ORDER BY tickets_non_resolus DESC, s.libelle;

Exercices

SELECT p.num_inventaire,
       COALESCE(CONCAT(e.prenom, ' ', e.nom), 'en stock') AS utilisateur
FROM poste p
LEFT JOIN employe e ON e.id_employe = p.id_employe
ORDER BY p.num_inventaire;

SELECT l.nom, i.date_installation
FROM employe e
JOIN poste p        ON p.id_employe  = e.id_employe
JOIN installation i ON i.id_poste    = p.id_poste
JOIN logiciel l     ON l.id_logiciel = i.id_logiciel
WHERE e.nom = 'Lefèvre' AND e.prenom = 'Julie'
ORDER BY l.nom;

SELECT l.nom, l.nb_licences,
       COUNT(i.id_poste) AS utilisees,
       l.nb_licences - COUNT(i.id_poste) AS restantes
FROM logiciel l
LEFT JOIN installation i ON i.id_logiciel = l.id_logiciel
WHERE l.nb_licences IS NOT NULL
GROUP BY l.id_logiciel, l.nom, l.nb_licences
ORDER BY restantes, l.nom;

SELECT r.nom, r.prenom, COUNT(*) AS nb_encadres
FROM employe e
JOIN employe r ON r.id_employe = e.id_responsable
GROUP BY r.id_employe, r.nom, r.prenom
ORDER BY nb_encadres DESC, r.nom;

SELECT s.libelle
FROM service s
LEFT JOIN employe e ON e.id_service = s.id_service
LEFT JOIN ticket t  ON t.id_demandeur = e.id_employe
WHERE t.id_ticket IS NULL;

SELECT s.libelle
FROM service s
LEFT JOIN employe e ON e.id_service = s.id_service
LEFT JOIN ticket t  ON t.id_demandeur = e.id_employe
GROUP BY s.id_service, s.libelle
HAVING COUNT(t.id_ticket) = 0
ORDER BY s.libelle;