SQL LEFT JOIN: find what is missing

Unmatched rows get NULL in the second table's columns. Add WHERE right_table.id IS NULL to keep only the rows with no match.

Put conditions on the second table in the ON clause, not in WHERE, or the LEFT JOIN quietly turns into an INNER JOIN.

Worked example

Which customers have never placed an order?

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

Common mistakes

• Filtering the right-hand table in WHERE.
• Counting with COUNT(*) instead of COUNT(o.id), which counts unmatched rows as 1.