The WHERE-on-Outer-Join Trap
Why filtering an outer-joined column in WHERE silently turns it into an inner join.
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';All lessons in this course
- LEFT JOIN and Preserving Unmatched Rows
- RIGHT and FULL OUTER JOIN Semantics
- Finding Rows With No Match (Anti-Join)
- The WHERE-on-Outer-Join Trap