Why did my LEFT JOIN start behaving like an INNER JOIN after I added a WHERE filter?

TL;DR: The LEFT JOIN pads unmatched right-side columns with NULL, then a WHERE predicate on those columns evaluates to UNKNOWN and discards the row, collapsing the outer join to an inner one. Move the right-table condition into the ON clause. The one exception is a deliberate IS NULL anti-join.

How to approach it

This is a conceptual-debugging question: explain the mechanism, show the fix, and name the general rule. A great answer takes 90 seconds and includes a two-line code contrast. If it's posed as "find the bug in this query," read the WHERE clause for any condition on the right (nullable) table first.

A strong answer

A LEFT JOIN preserves unmatched left-side rows by filling the right side's columns with NULL. If you then filter on a right-side column in WHERE:

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'completed';   -- bug

…every preserved row has o.status = NULL, and NULL = 'completed' evaluates to UNKNOWN, which WHERE treats as false. All the customers-without-orders you used the LEFT JOIN to keep are silently dropped, you've reinvented an INNER JOIN.

The fix depends on intent:

-- Intent: keep all customers, but only join completed orders
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'completed';     -- filter in ON

-- Intent: keep unmatched rows AND allow the filter
WHERE o.status = 'completed' OR o.order_id IS NULL;

Three customers are enough to watch the collapse happen (all three queries executed against the same fixture). Ana has a completed and a pending order, Bo has only a pending order, Cy has none:

plain LEFT JOIN:          (Ana, 10) (Ana, 11) (Bo, 12) (Cy, NULL)   -- 4 rows, all customers
+ WHERE status='completed': (Ana, 10)                               -- 1 row: Bo AND Cy gone
+ status moved into ON:    (Ana, 10) (Bo, NULL) (Cy, NULL)          -- 3 rows, all customers back

The middle line is the bug at full size: not only did Cy (no orders) vanish, so did Bo, whose only order failed the filter. A report titled "customers and their completed orders" just became "customers who have completed orders," and if it feeds a churn dashboard, the customers most likely to be churning are exactly the ones the query dropped. The third line is the fix keeping the promise: every customer appears, with order columns populated only where a completed order exists. This is also the row-count sanity check from the probes made concrete: the left table has 3 rows, so any result with fewer than 3 means the outer join is quietly inner.

The rule worth stating crisply: for outer joins, conditions on the inner (nullable) side belong in ON; conditions on the preserved side can live in WHERE. For INNER JOINs, ON vs WHERE placement doesn't change results (only readability), which is exactly why people develop the bad habit.

Bonus depth: the same NULL logic explains the NOT IN trap, WHERE id NOT IN (SELECT ref_id ...) returns zero rows if the subquery yields any NULL, so prefer NOT EXISTS or an anti-join (LEFT JOIN ... WHERE right.key IS NULL). Mentioning this unprompted shows you've actually debugged production SQL.

What interviewers probe next

• "When would you deliberately put a left-table filter in ON?", rare and confusing; it doesn't drop left rows in a LEFT JOIN, it just nulls the right side, so prefer WHERE for clarity.
• "How would you detect this bug in a deployed dashboard?", row-count sanity check: compare COUNT(*) against the left table alone; an unexpected drop after a LEFT JOIN means the filter or join is wrong.
• "Three-valued logic", TRUE/FALSE/UNKNOWN, and that NULL = NULL is UNKNOWN, motivating IS NULL / IS NOT DISTINCT FROM.
• Often chained into the join fan-out question (rows multiplying instead of disappearing).

Common mistakes

• Saying "WHERE runs before the join", backwards; the filter runs after, which is precisely why it kills the NULL-padded rows.
• Fixing with COALESCE(o.status,'completed') style hacks instead of moving the predicate.
• Not asking which behavior the user actually wanted, the fix differs.
• Forgetting the same trap exists in HAVING and in NOT IN.