Common mistake

SQL: Why a LEFT JOIN Turns Into an INNER JOIN

A null-rejecting WHERE filter on the right table removes unmatched LEFT JOIN rows. Move it into ON when those left rows should remain.

The symptom

You write a LEFT JOIN to keep every row from the left table, but unmatched rows disappear after you add a filter. The result looks exactly like an INNER JOIN.

Why it happens

A LEFT JOIN keeps unmatched left-table rows by filling the right-table columns with NULL. But WHERE o.status = 'shipped' runs after the join, and NULL = 'shipped' is not true. Those preserved unmatched rows get filtered away.

The join was left-outer at first. This particular null-rejecting WHERE condition removes the unmatched rows, making the result behave like an inner join for that predicate.

The fix

If the condition belongs to the matched right table but unmatched left rows should remain, move the condition into the ON clause. That limits which right-table rows can match without removing the left-table row.

Wrong: filtered in WHERE

select c.customer_id, c.first_name, o.order_id
from customers as c
left join orders as o on o.customer_id = c.customer_id
where o.status = 'shipped'
order by c.customer_id, o.order_id

Right: filtered in ON

select c.customer_id, c.first_name, o.order_id
from customers as c
left join orders as o
  on o.customer_id = c.customer_id
 and o.status = 'shipped'
order by c.customer_id, o.order_id

The extra rows are the six Garden Shop customers who have never had an order shipped. Filtered in ON, they survive with a NULL order_id; filtered in WHERE, they are gone. If you truly want only customers with shipped orders, use an INNER JOIN or keep the WHERE filter. The mistake is using LEFT JOIN while expecting unmatched rows to survive a right-side condition that rejects the generated NULLs.

Quick check

Add where o.order_id is null temporarily after the left join. If no rows appear, either every left row matched or a later filter removed the unmatched rows.