ON vs WHERE in Joins
When a predicate belongs in ON versus WHERE and why it matters for results.
ON vs WHERE in Joins is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
A Predicate Can Live in Two Places
Once you can write an INNER JOIN, the next interview question is sharper: does this condition belong in ON or in WHERE?
For an INNER JOIN the answer is often "it does not matter for the result." But the moment you switch to an outer join, the choice changes the answer completely. Interviewers ask this precisely because juniors put everything in WHERE out of habit.
This lesson makes the rule crisp.
What ON Does
The ON clause defines how rows are paired. It runs as the join is built, deciding which row from the left table matches which row from the right.
Think of ON as answering the question: "for these two rows, do they belong together?"
SELECT c.name, o.amount
FROM customers c
JOIN orders o
ON o.customer_id = c.id; -- pairing ruleWhat WHERE Does
The WHERE clause runs after the join has produced its combined rows. It filters that result set, discarding rows that fail the test.
Think of WHERE as answering: "now that I have joined rows, which of them do I want to keep?"
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 30; -- filter after pairingOn INNER JOIN They Often Agree
For an INNER JOIN, an extra filter gives the same result whether you put it in ON or WHERE. Both queries below return only Ada's $50 order and Bob's $99 order.
Because unmatched rows are already dropped by an inner join, moving the predicate does not change which rows survive.
-- predicate in ON
SELECT c.name, o.amount FROM customers c
JOIN orders o
ON o.customer_id = c.id AND o.amount > 30;
-- predicate in WHERE -- same result here
SELECT c.name, o.amount FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 30;Why Style Still Prefers ON for Join Keys
Even when results match, convention says: put join relationship conditions in ON and business filters in WHERE.
- ON:
o.customer_id = c.id(how tables relate) - WHERE:
o.amount > 30(which results you want)
This separation makes intent obvious to the next reader and to the interviewer grading your style.
Where It Really Matters: Outer Joins
The distinction becomes decisive with a LEFT JOIN, which keeps every left row even when no right row matches. A quick preview using customers and orders, where Cleo has no orders.
A LEFT JOIN keeps Cleo with NULL order columns. Now watch what ON versus WHERE does to her.
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- keeps Ada, Bob, AND Cleo (NULL amount)Filter in ON: Rows Are Preserved
Put the amount > 30 condition in ON of a LEFT JOIN and it only affects which right rows get attached. Unmatched left rows are still kept, just with NULLs.
Cleo survives. Any order that fails the test simply does not attach, leaving a NULL.
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id AND o.amount > 30;
-- Ada 50, Bob 99, Cleo NULL (3 rows, Cleo kept)Filter in WHERE: The Outer Join Collapses
Move the same condition to WHERE and you filter the joined result. Cleo's row has amount = NULL, and NULL > 30 is not true, so she is removed.
The LEFT JOIN silently behaves like an INNER JOIN. This is the single most famous join trap in interviews.
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 30;
-- Ada 50, Bob 99 (Cleo GONE -> back to inner-join behavior)The Rule to Memorize
State this cleanly in an interview:
- A condition in ON decides whether the right row attaches; left rows are preserved.
- A condition in WHERE filters the final rows and can eliminate preserved left rows when it touches a possibly-NULL column.
So for outer joins: predicates on the optional table go in ON unless you intentionally want to drop unmatched rows.
The Legitimate WHERE-on-NULL Use
There is one case where filtering an outer-joined column in WHERE is exactly right: the anti-join. Testing IS NULL finds left rows that had no match.
Here WHERE deliberately keeps only the unmatched rows, returning customers with zero orders. Same mechanics, opposite intent.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL; -- customers with no orders -> CleoQuick Mental Test
Before the quiz, run this checklist when you see a join condition:
- Is it a join key relating the tables? -> ON.
- Is it a filter on the required table? -> WHERE or ON both fine.
- Is it a filter on the optional (outer) table and you want unmatched rows kept? -> ON.
- Do you want to find non-matches? -> WHERE ... IS NULL.
Quick Check
Apply the ON-vs-WHERE rule to an outer join.
Recap: ON vs WHERE
Key points to carry into the interview:
- ON controls pairing and, for outer joins, whether the optional row attaches while preserving the kept side.
- WHERE filters the already-joined result and can drop preserved rows.
- On an INNER JOIN the two are often interchangeable; on outer joins they are not.
- Filters on the outer table belong in ON unless you intend an anti-join with
IS NULLin WHERE.
Frequently asked questions
Is the “ON vs WHERE in Joins” lesson free?
Yes — the full text of “ON vs WHERE in Joins” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “ON vs WHERE in Joins”?
When a predicate belongs in ON versus WHERE and why it matters for results. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “ON vs WHERE in Joins” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.