Lessons · SQL · a WHERE that undoes a LEFT JOIN
Where the filter goes in a LEFT JOIN
A condition on the right table in WHERE turns a LEFT JOIN into an inner join, because unmatched rows have NULL there and fail the test. Put that condition in the ON clause to keep the left rows.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
Every 'zeros included' report: cities with no paid orders, days with no signups. The difference between a correct report and one that silently drops the empty groups is where one condition sits.
How to think about it
Ask which rows must survive no matter what: those are the left table, and nothing in WHERE may test the right table. Conditions that qualify the match go in ON.
Worked example
SELECT c.city, COUNT(o.id) AS paid_ordersCOUNT(o.id) counts matches; a NULL match counts as zero.
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'The paid condition is part of the join, so a city with no paid orders keeps its row.
GROUP BY c.city;One row per city, zeros included.
Your turn
Keep every product, counting only orders over 50.
FROM products p LEFT JOIN orders o ON o.customer_id = p.id o.amount > 50
Run a query against real tables
The trap
WHERE o.status = 'paid' after a LEFT JOIN removes exactly the rows the LEFT JOIN was there to keep. It is the most common wrong answer to this question.