What is the difference between ON and WHERE when used with a LEFT JOIN?
SQLLevel: IntermediateJOINs Deep Dive
What is the difference between ON and WHERE when used with a LEFT JOIN?
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
This is a classic trap. Moving a filter from ON to WHERE changes the semantics for outer joins.
-- Filter in ON: still returns all customers, order_id will be NULL for customers
-- where the order status isn't 'completed'
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'completed';
-- Filter in WHERE: only returns customers who HAVE at least one completed order
-- (effectively turns the LEFT JOIN into an INNER JOIN)
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed';
For INNER JOIN, this doesn't matter. For outer joins, it's a real behavioral difference.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT