SQL: Why a LEFT JOIN Turns Into an INNER JOIN

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. The later WHERE condition made it behave like an inner join.

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.

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

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 aNULLorder_id; filtered in WHERE, they are gone. If you truly want only customers with shipped orders, use anINNER JOIN or keep the WHERE filter. The mistake is using LEFT JOIN while expecting unmatched rows to survive a right-sideWHERE condition.

Learn more

• Lesson 5.3 · INNER vs LEFT JOIN
• Reference · JOIN types
• Cheatsheet · Joins
• Mistake · Why my query returns no rows