Does a filter in the ON clause behave differently from WHERE in a LEFT JOIN?

Q: Does a filter in the ON clause behave differently from WHERE in a LEFT JOIN?

**Target Audience:** Anyone who has written a LEFT JOIN and been surprised by missing rows; interview candidates who need to explain why. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The behaviour is defined by the SQL standard and is the same in MySQL and SQL Server. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A teacher prints the class list and wants to write, next to each student, the tests they scored an A on. - **Version one:** go down the list; next to each name, write their A-grade tests. A student with no A gets an empty space. Everybody is still on the lis

The problem

Question presented to candidate: "I want every customer with their paid orders, including customers who have none. I wrote LEFT JOIN orders ... WHERE o.status = 'paid' and some customers vanished. Why? Does it matter whether a condition goes in the ON clause or the WHERE clause?"

What a strong answer should cover:

For an INNER JOIN it does not matter: ON and WHERE give the same rows
For a LEFT JOIN it matters: ON decides which right rows match; unmatched left rows are still kept and padded with NULLs. WHERE runs afterwards and removes rows, including those NULL-padded ones
So a right-table filter in WHERE turns the LEFT JOIN into an inner join (PostgreSQL even plans it as a plain Hash Join)
A left-table filter in ON does not remove left rows; it only stops them from matching. To filter the left table, use WHERE
WHERE o.status = 'paid' OR o.status IS NULL is not a fix: it keeps customers with no orders but still drops customers whose orders are all unpaid
The deliberate use of WHERE on the right table: WHERE o.id IS NULL for an anti-join

Code / implementation expected: Yes — the same LEFT JOIN with the filter in ON and in WHERE, and the different results.

Schema used by the examples:

joins
left-join

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Anyone who has written a LEFT JOIN and been surprised by missing rows; interview candidates who need to explain why. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The behaviour is defined by the SQL standard and is the same in MySQL and SQL Server.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A teacher prints the class list and wants to write, next to each student, the tests they scored an A on.

Version one: go down the list; next to each nam

The solution is hidden so you can try first

12 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLFilter in ON: every customer kept, only paid orders attached

SQLSame filter in WHERE: customers without a paid order disappear

SQLTrap: a condition on the LEFT table in ON does not filter customers

PostgreSQL: with the filter in WHERE, the plan is a plain inner Hash Join

EXPLAIN (COSTS OFF)
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid';

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.