A LEFT JOIN silently acts like an INNER JOIN — a right-table filter is in WHERE. Fix it.
This query should list every customer with the amount of their completed orders, keeping customers who have none (amount NULL). Instead it drops every customer without a completed order — the LEFT JOIN behaves like an INNER JOIN.
SELECT c.customer_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'completed';Find and fix the bug without losing any customers.
The WHERE o.status = 'completed' runs after the join and drops the NULL-padded unmatched rows, so the LEFT JOIN collapses to an INNER JOIN. Move the right-table predicate into the join: LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'completed'.
- ✗Filtering the right table in WHERE after a LEFT JOIN
- ✗Blaming the LEFT JOIN keyword instead of the WHERE timing
- ✗Thinking ON may reference only the join-key columns
- →When is a right-table predicate in WHERE actually correct?
- →How does an IS NULL check in WHERE differ from moving it to ON?
The LEFT JOIN produces NULL-padded rows for customers with no matching order. But WHERE runs after the join, and o.status = 'completed' is NULL (not 'completed') on those padded rows, so they fail the filter and disappear — the query effectively becomes an inner join.
Move the right-table condition into the join predicate so it is applied while matching, not after:
SELECT c.customer_id, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'completed'
ORDER BY c.customer_id;Now every customer is kept: those with a completed order show its amount, and those without show NULL. Rule of thumb — for a preserved (outer) side, put filters on the optional table in ON; keep filters on the preserved table in WHERE.
