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_orders

COUNT(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

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.

Practise a WHERE that undoes a LEFT JOIN on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.