Why does filtering on a right-table column in the WHERE clause turn a LEFT JOIN into an INNER JOIN?

How to think about it

This is a top-five SQL correctness trap — in interviews and in production. The query looks like a LEFT JOIN but behaves exactly like an INNER JOIN, and the difference only surfaces when you notice that unmatched rows have silently vanished. The reasoning to say aloud is the execution order: FROM + JOIN runs first (producing NULLs for unmatched rows), then WHERE filters the combined result — and a filter on a right-table column deletes precisely the NULL rows the LEFT JOIN was meant to keep.

A worked example — the vanishing rows

Intent: “every customer, with their paid orders if any.” Written with the filter in WHERE, it quietly drops the customers who don’t qualify:

-- BROKEN: WHERE on a right-table column kills the LEFT JOIN
SELECT c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY c.name;

name	order_id	status
Aarav	101	paid
Bea	103	paid

Chen (only a pending order) and Dana (no order at all) are gone. For them o.status is NULL, and NULL = 'paid' is UNKNOWN — treated as false in WHERE, so the rows are eliminated. That’s an INNER JOIN wearing a LEFT JOIN’s clothes.

The fix — move the filter into ON

Put the order condition in the ON clause, where it filters which orders match during the join rather than filtering the result afterward:

SELECT c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id
 AND o.status = 'paid'
ORDER BY c.name;

name	order_id	status
Aarav	101	paid
Bea	103	paid
Chen	NULL	NULL
Dana	NULL	NULL

Now all four customers stay. Chen and Dana come back with NULLs, because the join found no paid order for them — which is exactly what “all customers, with paid orders if any” should mean.

When WHERE is actually correct

Filtering in WHERE is right when you want to drop unmatched rows — or when you want only the unmatched ones. WHERE o.order_id IS NULL after a LEFT JOIN is the anti-join: customers who never ordered.

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

name
Dana

Only Dana — the one customer with no order at all.