Lesson 14 of 15 · 18 min
ON versus WHERE in outer joins
By the end of this lesson you can predict how moving a condition between ON and WHERE changes the result of a LEFT JOIN, filter the optional table inside ON to keep zero rows, and filter the preserved table in WHERE.
For an inner join it makes no difference whether a condition sits in ON or in WHERE: a row survives only if every condition is true, wherever you write it. For an outer join the two clauses do different jobs:
- ON decides which rows match. A LEFT JOIN evaluates ON for each pair. Left rows that find no pair satisfying ON are still kept, with NULLs on the right.
- WHERE decides which rows survive. It runs after the join has built its result, NULL-filled rows included, and removes every row for which the condition is not true.
So the same condition means two different things:
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'→ every customer, matched only to their delivered orders.LEFT JOIN orders o ON o.customer_id = c.customer_id … WHERE o.status = 'delivered'→ only customers who have a delivered order.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons