SQL LEFT JOIN: keep the rows without a match

With an INNER JOIN, a customer who never ordered disappears from the result. Yet sometimes that is exactly who you are looking for: inactive customers, products that never sold, suppliers without products… LEFT JOIN solves this problem: it keeps every row of the left table, whether it has a match or not.

Syntax

SELECT a.column, b.column
FROM table_a a
LEFT JOIN table_b b ON b.key = a.key;

How it works

Left and right

The “left” table is the one written after FROM; the “right” table is the one in the LEFT JOIN. Every row on the left is kept. When a left row has no match on the right, the right columns are NULL.

LEFT JOIN or INNER JOIN?

Finding what is missing

The most useful LEFT JOIN pattern: LEFT JOIN … WHERE right.id IS NULL. It only keeps the left rows without any match: customers without orders, products never sold, and so on.

RIGHT JOIN and FULL JOIN

RIGHT JOIN does the same thing while keeping the right table; you can always write it as a LEFT JOIN by swapping the tables. FULL OUTER JOIN keeps the rows of both sides. SQLite accepts them since version 3.39.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT c.id, c.prenom, c.nom, COUNT(o.id) AS nb_commandes
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
GROUP BY c.id
ORDER BY nb_commandes
LIMIT 8;

SELECT c.id, c.prenom, c.nom, c.ville
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
WHERE o.id IS NULL;

SELECT
  (SELECT COUNT(DISTINCT c.id) FROM clients c JOIN commandes o ON o.client_id = c.id) AS avec_inner_join,
  (SELECT COUNT(DISTINCT c.id) FROM clients c LEFT JOIN commandes o ON o.client_id = c.id) AS avec_left_join;

SELECT e.prenom, e.nom, e.poste
FROM employes e
LEFT JOIN commandes o ON o.employe_id = e.id
WHERE o.id IS NULL;

Common mistakes

SELECT c.prenom, o.id FROM clients c LEFT JOIN commandes o ON o.client_id = c.id WHERE o.statut = 'annulée';

SELECT c.prenom, o.id FROM clients c LEFT JOIN commandes o ON o.client_id = c.id AND o.statut = 'annulée';

SELECT c.id, COUNT(*) FROM clients c LEFT JOIN commandes o ON o.client_id = c.id GROUP BY c.id;

SELECT c.id, COUNT(o.id) FROM clients c LEFT JOIN commandes o ON o.client_id = c.id GROUP BY c.id;

SELECT c.nom FROM commandes o LEFT JOIN clients c ON c.id = o.client_id WHERE o.id IS NULL;

SELECT c.nom FROM clients c LEFT JOIN commandes o ON o.client_id = c.id WHERE o.id IS NULL;

Exercises

Try writing the query yourself before opening the solution.

SELECT f.nom, COUNT(p.id) AS nb_produits FROM fournisseurs f LEFT JOIN produits p ON p.fournisseur_id = f.id GROUP BY f.id;

SELECT e.prenom, e.nom, COUNT(o.id) AS nb_commandes FROM employes e LEFT JOIN commandes o ON o.employe_id = e.id GROUP BY e.id ORDER BY nb_commandes LIMIT 8;

Frequently asked questions