0Pricing
SQL Interview Prep · Lesson

The WHERE-on-Outer-Join Trap

Why filtering an outer-joined column in WHERE silently turns it into an inner join.

The WHERE-on-Outer-Join Trap is a free SQL Interview Prep lesson on CoddyKit — lesson 4 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.

The Trap That Catches Everyone

This is the single most common outer-join bug interviewers plant: "Show every customer and their orders from 2024, including customers with no 2024 orders."

A candidate writes a LEFT JOIN, then adds a date filter in WHERE, and the customers with no 2024 orders silently vanish. The LEFT JOIN quietly degrades into an INNER JOIN. Understanding why is a senior-level signal.

The Buggy Query

Here is the mistake. It looks reasonable: keep all customers, join their orders, filter to 2024.

But customers with no orders, or no 2024 orders, disappear from the result. The requirement to include them is violated.

-- BUG: drops customers with no 2024 order
SELECT c.name, o.id, o.order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01';

Why It Breaks

Recall the order of operations: the JOIN happens first, producing rows where unmatched customers have NULL in every order column. Then WHERE runs.

For an unmatched customer, o.order_date is NULL, so o.order_date >= '2024-01-01' evaluates to UNKNOWN, not true. WHERE keeps only rows that are true, so the NULL rows are filtered out, exactly the rows the LEFT JOIN worked to preserve.

NULL Defeats the Filter

Any comparison against NULL yields UNKNOWN: NULL >= '2024-01-01' is UNKNOWN, NULL = 5 is UNKNOWN, even NULL <> 5 is UNKNOWN.

Since WHERE passes only rows evaluating to TRUE, every preserved no-match row gets discarded. The outer join's whole purpose is undone by a single WHERE predicate on a right-table column.

The Fix: Filter in ON

Move the filter into the ON clause. There it becomes part of the match condition, applied before rows are preserved, so unmatched customers still survive with NULLs.

-- CORRECT: filter lives in ON
SELECT c.name, o.id, o.order_date
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
  AND o.order_date >= '2024-01-01';
-- customers with no 2024 order: kept, NULL order

ON vs WHERE in One Sentence

The rule to recite in an interview:

For the preserved (outer) table, conditions on the other table belong in ON; conditions on the preserved table itself belong in WHERE.

  • ON decides what counts as a match (runs during the join).
  • WHERE filters the final rows (runs after, and removes NULL rows).

Side-by-Side Results

Same data, two placements, different answers. Suppose Carol has no 2024 order.

  • Filter in WHERE: Carol is gone. Effectively an inner join.
  • Filter in ON: Carol appears once with NULL order columns, the requirement is met.

The output difference is the whole point of the trap.

-- ON version output
-- Alice | 50 | 2024-03-01
-- Bob   | 20 | 2024-05-02
-- Carol | NULL | NULL   <-- preserved

When WHERE Is Actually Correct

Not every WHERE on an outer join is a bug. Filtering the preserved table is fine, it does not involve NULLs from the join.

And the anti-join from the previous lesson intentionally uses WHERE o.id IS NULL to exploit this very behavior. The skill is knowing which case you are in.

-- Fine: filtering the preserved (left) table
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'US';

The Detection Heuristic

When reviewing an outer join, scan the WHERE clause for predicates on the non-preserved table (other than IS NULL anti-join tests).

If you see o.someColumn = ... or a range/equality test on the outer side in WHERE, suspect the trap. Ask: "Does this turn my LEFT JOIN into an INNER JOIN?" Usually it does.

Multiple Conditions

You can combine both placements. Match conditions on the right table go in ON; a genuine post-join filter on the left table goes in WHERE. They coexist cleanly.

SELECT c.name, o.id, o.amount
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
  AND o.amount > 100          -- match condition
WHERE c.signup_year = 2023;    -- preserved-table filter

Explaining It Out Loud

In the interview, narrate the mechanism, not just the fix:

"The join runs first and fills unmatched right columns with NULL. A WHERE predicate on those columns evaluates to UNKNOWN for the NULL rows, and WHERE drops non-true rows, so the outer join collapses to an inner join. Putting the predicate in ON keeps it as a match condition and preserves the unmatched rows." That explanation lands every time.

Quick Check

You must list all customers and only their 2024 orders, keeping customers who had none.

Recap

Filtering a non-preserved table's column in WHERE silently turns an outer join into an inner join, because NULLs from unmatched rows fail the predicate (UNKNOWN) and WHERE drops them.

  • Match conditions on the outer table go in ON.
  • Filters on the preserved table go in WHERE.
  • IS NULL in WHERE is the intentional anti-join, not the trap.
  • Explain the order of operations to prove you understand it.

Frequently asked questions

Is the “The WHERE-on-Outer-Join Trap” lesson free?

Yes — the full text of “The WHERE-on-Outer-Join Trap” 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 “The WHERE-on-Outer-Join Trap”?

Why filtering an outer-joined column in WHERE silently turns it into an inner join. 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 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “The WHERE-on-Outer-Join Trap” 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.

All lessons in this course

  1. LEFT JOIN and Preserving Unmatched Rows
  2. RIGHT and FULL OUTER JOIN Semantics
  3. Finding Rows With No Match (Anti-Join)
  4. The WHERE-on-Outer-Join Trap
← Back to SQL Interview Prep