Docs SQLite exécutables : LEFT JOIN

Q: À quoi sert un LEFT JOIN en SQLite ?

Un LEFT JOIN renvoie toutes les lignes de la table de gauche, et y associe les lignes correspondantes de la table de droite quand il y en a. S'il n'y a pas de correspondance, la ligne de gauche est quand même retournée et les colonnes de droite valent NULL. À noter : LEFT OUTER JOIN fait exactement la même chose, le mot-clé OUTER est facultatif en SQLite.

Q: Quelle différence entre LEFT JOIN et INNER JOIN en SQLite ?

L'INNER JOIN ne ramène que les lignes pour lesquelles la condition de jointure est satisfaite des deux côtés. Le LEFT JOIN, lui, ramène toutes les lignes de la table de gauche, et remplit avec NULL les colonnes de droite quand il n'y a pas de correspondance. Le LEFT JOIN est utile dès que l'absence de correspondance a un sens — par exemple, lister les utilisateurs qui n'ont passé aucune commande.

Q: Pourquoi mon LEFT JOIN se comporte comme un INNER JOIN ?

Dans la quasi-totalité des cas, c'est à cause d'une clause WHERE qui filtre une colonne de la table de droite sans tenir compte des NULL. Les conditions sur la table de droite doivent aller dans le ON, pas dans le WHERE — ou alors il faut écrire WHERE droite.col IS NULL pour récupérer les lignes sans correspondance. Un WHERE droite.col = 'x' élimine silencieusement toutes les lignes non appariées.

LEFT JOIN : tout garder côté gauche

INNER JOIN ne renvoie que les lignes qui ont une correspondance des deux côtés. C'est souvent ce qu'on cherche, mais pas toujours. Parfois, c'est justement l'absence de correspondance qui nous intéresse : les utilisateurs qui n'ont jamais passé commande, les produits qui ne se sont jamais vendus, les articles sans aucun commentaire. Pour ces cas-là, il faut passer par un LEFT JOIN.

Une jointure LEFT JOIN (ou LEFT OUTER JOIN) renvoie toutes les lignes de la table de gauche. Si la table de droite contient une ligne qui correspond, vous récupérez les colonnes associées. Sinon, la ligne de gauche apparaît quand même, et les colonnes de droite sont remplies avec NULL.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
);

INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cléo');
INSERT INTO orders (user_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);

SELECT u.name, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

Cleo n'a passé aucune commande, mais elle apparaît quand même — avec NULL dans la colonne total. Remplacez LEFT JOIN par INNER JOIN et Cleo disparaît purement et simplement.

Le modèle mental

Lisez la requête de haut en bas et voyez la table de gauche comme l'ancre. Chaque ligne de users va se retrouver dans le résultat, quoi qu'il arrive. Le LEFT JOIN se contente ensuite de poser la question, pour chaque utilisateur : « existe-t-il une ligne correspondante dans orders ? »

Correspondance trouvée → on colle les colonnes correspondantes sur la ligne de l'utilisateur.
Plusieurs correspondances → une ligne de sortie par correspondance (Ada a deux commandes, elle apparaît donc deux fois).
Aucune correspondance → une seule ligne, avec NULL pour toutes les colonnes de la table de droite.

C'est précisément ce dernier cas qui justifie l'existence du LEFT JOIN. Ici, NULL ne signifie pas « on ne sait pas » — il veut dire « il n'y a rien à coller du côté droit ».

LEFT OUTER JOIN, c'est exactement la même opération. Le mot-clé OUTER est optionnel en SQLite, et la plupart des gens le laissent de côté.

Trouver les lignes sans correspondance

Le cas d'usage classique du LEFT JOIN : repérer les lignes de la table de gauche qui n'ont aucune correspondance à droite. L'astuce consiste à filtrer sur une colonne de la table de droite qui est NOT NULL dans les vraies données — en général sa clé primaire — et à tester NULL après la jointure :

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 2, 99.00);

SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

Seule Cleo ressort. La jointure rattache les commandes lorsqu'elles existent, puis le WHERE o.id IS NULL ne garde que les lignes où ce rattachement a échoué. On parle parfois d'« anti-jointure ».

ON vs WHERE : le piège subtil

C'est le bug le plus fréquent avec LEFT JOIN, et ça vaut le coup de s'y arrêter. Les conditions peuvent aller soit dans la clause ON, soit dans la clause WHERE, mais leur comportement diffère complètement avec les jointures externes.

ON s'applique pendant la jointure. Les conditions décident ici quelles lignes de la table de droite sont considérées comme une correspondance.
WHERE s'applique après que la jointure a produit ses lignes. Cela filtre le résultat combiné.

Regardez ce qui se passe quand vous mettez une condition sur la table de droite dans WHERE :

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

-- Incorrect : transforme silencieusement le LEFT JOIN en INNER JOIN
SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'shipped';

Cleo n'a aucune commande : sur sa ligne, o.status vaut donc NULL, et NULL = 'shipped' n'est pas vrai — elle se fait filtrer. Pour Boris, le statut est 'pending', exclu lui aussi. Du coup, votre LEFT JOIN s'est sournoisement transformé en INNER JOIN.

La parade : déplacer la condition dans le ON, pour qu'elle filtre les correspondances et non les lignes du résultat :

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'shipped';

Maintenant, tous les utilisateurs apparaissent. Ada obtient sa commande expédiée ; Boris obtient NULL (sa commande en attente ne correspondait pas au critère) ; Cleo obtient NULL (aucune commande du tout). C'est exactement ce qu'on veut quand la question est : « montre-moi tous les utilisateurs, avec leurs commandes expédiées s'il y en a ».

Règle pratique : les conditions sur la table de gauche peuvent rester dans le WHERE. Les conditions sur la table de droite, elles, ont presque toujours leur place dans le ON — sauf si vous cherchez justement à isoler les lignes sans correspondance avec IS NULL.

Compter avec LEFT JOIN

Un cas classique de jointure SQL : compter les lignes liées pour chaque parent, y compris ceux qui n'en ont aucune. Avec un INNER JOIN, les zéros disparaissent. Un LEFT JOIN combiné à un COUNT sur une colonne de la table de droite donne le bon résultat :

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 1, 12.50), (3, 2, 99.00);

SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS revenue
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

Deux points méritent qu'on s'y attarde :

COUNT(o.id) compte uniquement les lignes non nulles côté droit. Cleo obtient 0, et non 1 — parce que COUNT ignore les NULL. Avec COUNT(*), Cleo aurait eu 1 (la ligne existe, elle contient juste des NULL). Dans la quasi-totalité des cas, c'est COUNT(right.id) que vous voulez.
COALESCE(SUM(o.total), 0) transforme le NULL de Cleo en 0. Sans ça, son chiffre d'affaires apparaîtrait comme NULL : techniquement juste, mais moche à afficher.

Jointure SQL sur plusieurs tables

Les LEFT JOIN se chaînent. Chaque jointure reprend le résultat courant et y greffe une nouvelle table. Dès qu'une colonne devient nullable à cause d'un LEFT JOIN, gardez du LEFT JOIN pour toutes les tables qui en dépendent — sinon le prochain INNER JOIN va silencieusement éliminer les lignes que vous vouliez conserver.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);
CREATE TABLE shipments (order_id INTEGER PRIMARY KEY, carrier TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (10, 1, 49.99), (11, 2, 99.00);
INSERT INTO shipments VALUES (10, 'UPS');

SELECT u.name, o.total, s.carrier
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
LEFT JOIN shipments AS s ON s.order_id = o.id;

Trois utilisateurs ressortent. Ada a une commande et une expédition. Boris a une commande mais aucune expédition (le transporteur vaut NULL). Cleo n'a aucune commande, donc o.total et s.carrier valent tous les deux NULL. L'enchaînement des LEFT JOIN conserve chaque utilisateur, peu importe à quel maillon de la chaîne les données viennent à manquer.

Quand utiliser un LEFT JOIN en SQLite

Optez pour LEFT JOIN quand la question posée porte avant tout sur la table de gauche, la table de droite n'apportant qu'un complément d'information. Des formulations comme « tous les utilisateurs, avec leurs commandes s'ils en ont » ou « tous les produits et leur dernier avis » se traduisent directement par un LEFT JOIN.

Préférez INNER JOIN quand les deux côtés sont indispensables — « les commandes avec les détails de leur utilisateur » n'a pas de sens pour une commande sans utilisateur, donc le filtrage de la jointure interne est exactement ce qu'il faut.

Si vous vous retrouvez à écrire LEFT JOIN ... WHERE right.col IS NOT NULL, c'est qu'en réalité vous vouliez un INNER JOIN. Si vous écrivez LEFT JOIN ... WHERE right.col IS NULL, vous cherchez une anti-jointure, et là c'est bien ce qu'il faut faire.

La suite : les auto-jointures

Parfois, la table que vous voulez joindre est celle que vous interrogez déjà — les employés et leurs managers, les catégories et leurs parents, des paires d'utilisateurs vivant dans la même ville. C'est ce qu'on appelle une auto-jointure (self-join), et c'est le sujet de la page suivante.

Questions fréquentes

À quoi sert un LEFT JOIN en SQLite ?

Un LEFT JOIN renvoie toutes les lignes de la table de gauche, et y associe les lignes correspondantes de la table de droite quand il y en a. S'il n'y a pas de correspondance, la ligne de gauche est quand même retournée et les colonnes de droite valent NULL. À noter : LEFT OUTER JOIN fait exactement la même chose, le mot-clé OUTER est facultatif en SQLite.

Quelle différence entre LEFT JOIN et INNER JOIN en SQLite ?

L'INNER JOIN ne ramène que les lignes pour lesquelles la condition de jointure est satisfaite des deux côtés. Le LEFT JOIN, lui, ramène toutes les lignes de la table de gauche, et remplit avec NULL les colonnes de droite quand il n'y a pas de correspondance. Le LEFT JOIN est utile dès que l'absence de correspondance a un sens — par exemple, lister les utilisateurs qui n'ont passé aucune commande.

Pourquoi mon LEFT JOIN se comporte comme un INNER JOIN ?

Dans la quasi-totalité des cas, c'est à cause d'une clause WHERE qui filtre une colonne de la table de droite sans tenir compte des NULL. Les conditions sur la table de droite doivent aller dans le ON, pas dans le WHERE — ou alors il faut écrire WHERE droite.col IS NULL pour récupérer les lignes sans correspondance. Un WHERE droite.col = 'x' élimine silencieusement toutes les lignes non appariées.

Concepts liés

SQLite LEFT JOIN : garder toutes les lignes de gauche