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-5 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 unmatched rows have silently vanished.

Walk the interviewer through the execution order: FROM + JOIN runs first (producing NULLs for unmatched rows), then WHERE filters the combined result. A filter on a right-table column eliminates every row where the right side was NULL — exactly the rows LEFT JOIN was supposed to keep.

The broken pattern

-- Intent: all customers, but only include paid orders (or no order at all)
-- Actual result: customers with no orders are silently dropped
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';           -- <-- kills the LEFT JOIN

For customers with no orders, o.status is NULL. NULL = 'paid' evaluates to UNKNOWN, which is treated as FALSE in a WHERE clause — those rows are eliminated.

The fix: move the filter into the ON clause

-- Correct: unmatched customers are retained; the ON clause filters which orders qualify
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';

Now the join first finds all paid orders for each customer. Customers with no paid orders still appear — with NULL in order_id and status.

When WHERE is correct

If you genuinely want to exclude unmatched rows — i.e. you want INNER JOIN behaviour — then filtering in WHERE is correct. Use WHERE o.id IS NULL to get the opposite: only unmatched rows (anti-join pattern).

-- Customers who have NEVER placed any order
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

See it yourself — spot the vanishing row

Run the query below. “Dana” has no paid orders. With WHERE on the right-table column, Dana disappears. Move the filter to the ON clause to keep Dana with NULLs.

Quick mental model

Think of execution order: FROM + JOIN happens first (producing NULLs for unmatched rows), then WHERE filters the combined result set. The ON clause runs during the join, so it can filter which orders qualify without dropping unmatched customers.