Skip to content
Quantoros

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

Related questions