SQL LEFT JOIN with WHERE: Why Rows Disappear

Start with the version that looks reasonable but answers a different question: which customers have a paid order? Ada qualifies. Grace's existing order fails the status condition. Linus receives a NULL-extended order row during the outer join, and comparing its status with 'paid' is unknown rather than true.

SQL's WHERE clause keeps only rows for which its condition is true. False and unknown are both discarded. That is why this particular right-table filter gives the same surviving matches as an inner join with the paid condition. PostgreSQL may even represent that equivalence in its plan; the word LEFT in your source does not protect rows from a later filter.

Inspect the returned names before thinking about a fix. There is one row, Ada, even though customers still contains all three people. No rows were deleted from the table. The difference is entirely in the result of this SELECT.

psql

SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id;

Now move the paid-order requirement into the join condition. An order matches only when both its customer ID and its status qualify. For Grace, the cancelled order no longer matches. The LEFT JOIN therefore supplies an empty order side for her, just as it does for Linus.

The result contains Ada with a paid order, then Grace and Linus with NULL order fields. COALESCE makes those NULLs readable here; it changes their display, not which rows the join preserves.

Think of ON as deciding whether a right-hand row belongs beside this customer. Think of WHERE as deciding whether the resulting row belongs in the report. That distinction is more useful than a blanket rule to move every condition into ON.

One detail remains: LEFT JOIN preserves left rows, but does not promise exactly one result per customer. If Ada had two paid orders, she would appear twice. Use an aggregate, a qualifying EXISTS condition, or an explicit rule for choosing one order when your report needs one row per customer. Moving the filter does not resolve a many-to-one reporting requirement.

SELECT c.name, COALESCE(o.status, 'no paid order') AS paid_order
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id;

A frequent repair leaves the filter in WHERE and adds OR o.id IS NULL. Run it before assuming it is equivalent to the ON version.

Linus returns because his order ID is NULL. Grace still disappears: she matched a real cancelled order, so her order ID is not NULL, and her status is not paid. The WHERE clause removes that row; it does not go back and ask the join to manufacture a replacement NULL row.

This query has a legitimate meaning: return paid orders or customers with no orders at all. That differs from returning every customer and showing a paid order where available. Also notice that we test the order's non-null primary key, rather than a nullable descriptive field. An actual order with a NULL description would otherwise be mistaken for no order.

When debugging your own report, write down the expected outcome for these same three cases: qualifying child, nonqualifying child, and no child. A tiny fixture often reveals the intended semantics more clearly than a large production result. Keep the fixture in a regression test once the report is correct.

SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid' OR o.id IS NULL
ORDER BY c.id;