· 6 min read
Why a WHERE clause turns your LEFT JOIN into an INNER JOIN
A WHERE filter on the right table of a LEFT JOIN silently drops unmatched rows. Here is why NULL causes it and why moving the filter to ON fixes it.

If you filter the right-hand table of a LEFT JOIN in the WHERE clause, rows that had no match disappear, and the query behaves exactly like an INNER JOIN. That happens because unmatched rows carry NULL in every right-side column, and a condition like o.status = 'paid' is never true for NULL. Move the filter into the ON clause and the unmatched rows come back.
The query still runs, still returns sensible-looking rows, and nothing warns you. The missing rows are usually the ones the LEFT JOIN was written to keep.
Reproduce it
Three customers, three orders. Chen has no orders, and Ben has only a refunded one:
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, total INTEGER);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen');
INSERT INTO orders VALUES
(10, 1, 'paid', 50),
(11, 1, 'refunded', 20),
(12, 2, 'refunded', 30);
The goal is a list of every customer with their paid orders, showing customers with none as empty. The natural first attempt:
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';
Asha | 10
One row. Ben and Chen are gone, even though the whole point of LEFT JOIN is to keep every row from customers.
Why does WHERE turn a LEFT JOIN into an INNER JOIN?
A LEFT JOIN (short for left outer join) returns every row from the left table. When a left row has no matching right row, the database still emits it and fills the right-side columns with NULL.
The order of operations matters. Logically, the join happens first, and WHERE filters the joined result afterwards. Before the WHERE runs, the intermediate result looks like this:
Asha | 10 | paid
Asha | 11 | refunded
Ben | 12 | refunded
Chen | NULL | NULL
Now the filter o.status = 'paid' is applied to each row. Asha's refunded order fails, which is expected. Ben's row fails for the same reason. Chen's row is the interesting one: comparing NULL to 'paid' gives UNKNOWN, not true, and WHERE keeps only rows where the condition is true. SQL uses three-valued logic, meaning a condition can be true, false or unknown, and any ordinary comparison with NULL is unknown.
So every row the outer join added gets thrown away. What is left is exactly what an inner join would have returned. A condition that can never be true when its columns are NULL is sometimes called null-rejecting, and many query planners, PostgreSQL's among them, notice this and quietly plan the query as an inner join.
The same thing happens with <>, >, IN, LIKE and most other predicates on a right-side column. WHERE o.status <> 'refunded' drops Chen too.
How do you filter a LEFT JOIN without losing rows?
Put conditions on the right-hand table in the ON clause:
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.status = 'paid';
Asha | 10
Ben | NULL
Chen | NULL
The ON clause decides which right rows count as a match. Ben's refunded order no longer matches, so Ben is treated like a customer with no orders and still appears, with NULL on the right. Chen appears for the same reason.
The rule that covers almost every case:
| Condition is on | Put it in | Effect |
|---|---|---|
| The right (optional) table | ON | Narrows what counts as a match; left rows are kept |
| The left (preserved) table | WHERE | Removes left rows from the result |
Conditions on the left table behave differently in ON. Writing ON o.customer_id = c.id AND c.name = 'Asha' does not filter the result down to Asha. It only stops other customers from matching any orders, so Ben and Chen still appear with NULL columns. If you want fewer left rows, that condition belongs in WHERE.
Why doesn't "OR o.status IS NULL" fix it?
A common patch is to allow the NULL rows through explicitly:
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid' OR o.status IS NULL;
Asha | 10
Chen | NULL
Chen is back, but Ben is still missing. Ben did match an order in the join, a refunded one, so his row has status = 'refunded', not NULL, and the filter removes it. This version only keeps customers with zero orders of any kind, which is a different question from customers with zero paid orders. It looks fixed on small test data where nobody has the in-between case, which is why it survives code review.
There is a second problem with the IS NULL check: it cannot tell a missing match from a real row whose status column happens to be NULL. If you ever need to test for "no match", check the right table's primary key or join key, which is never NULL on a real row.
The anti-join and aggregate versions of the same bug
The pattern shows up in two other common queries.
An anti-join returns left rows that have no matching right row. The usual form is a LEFT JOIN plus WHERE right_key IS NULL. Here the IS NULL in WHERE is correct, and the extra filter still belongs in ON:
-- Customers with no paid orders
SELECT c.name
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.status = 'paid'
WHERE o.id IS NULL;
-- Ben, Chen
Move o.status = 'paid' into WHERE and the query returns nothing, because the two conditions can never both be true. NOT EXISTS expresses the same thing and avoids the question of where the filter goes. It also avoids the NULL trap described in why SQL NOT IN returns no rows when the subquery has a NULL.
Aggregates over a LEFT JOIN have their own detail. Each unmatched left row is still one row, so COUNT(*) gives 1 for a customer with no orders. Count a right-side column instead, since COUNT(column) skips NULL:
SELECT c.name, COUNT(*) AS rows, COUNT(o.id) AS paid_orders,
COALESCE(SUM(o.total), 0) AS paid_total
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.status = 'paid'
GROUP BY c.name;
Asha | 1 | 1 | 50
Ben | 1 | 0 | 0
Chen | 1 | 0 | 0
SUM over only NULL values returns NULL, not 0, so wrap it in COALESCE if the result feeds a chart or a total.
All of the output above comes from SQLite, but this is standard SQL behavior and PostgreSQL, MySQL and SQL Server follow the same rules. ORMs and query builders are not exempt either. A .where() chained after a left join usually compiles to a WHERE clause, so look for an option that adds the condition to the join itself.
Key takeaways
- A
WHEREcondition on a right-side column of aLEFT JOINremoves the unmatched rows, turning it into an inner join. - Conditions on the optional table belong in
ON; conditions on the preserved table belong inWHERE. OR column IS NULLonly brings back left rows with no match at all, not rows whose matches were all filtered out.- To find rows with no match, test the right table's key for
NULL, or useNOT EXISTS. - After a
LEFT JOIN, count a right-side column, not*, and wrap sums inCOALESCE.
FAQ
Does this apply to RIGHT JOIN and FULL OUTER JOIN?
Yes. In a RIGHT JOIN the roles swap, so filters on the left table belong in ON. In a FULL OUTER JOIN both sides can be NULL, so a WHERE filter on either side removes rows that outer join added.
Is there any difference between ON and WHERE for an INNER JOIN?
Not in the result. For an inner join, a condition gives the same rows in either place, so choose whichever reads more clearly. The difference only exists for outer joins.
Is the ON version slower?
Usually not. The database still uses indexes on the join and filter columns, and filtering during the join can mean fewer rows to process. Check the query plan if a specific query is slow.