Data · SQL
Why WHERE turns a LEFT JOIN into an INNER JOIN
Four orders of $40, $60, $20 and $50: how many pass amount > 30?
And if the $20 amount becomes NULL?
From a filter on amounts to two versions of the same query, in five steps:
When does SQL apply the WHERE clause of a join?
WHERE runs after the join, on each row of its result. An order of $12 comes out of the join, then WHERE amount > 30 drops its row.
Let’s take three customers and their orders.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $40 |
| 102 | 1 | $70 |
| 103 | 2 | $25 |
| 104 | 2 | $60 |
| 105 | 3 | $50 |
| name | amount |
|---|---|
| Emma | $40 |
| Emma | $70 |
| Noah | $25 |
| Noah | $60 |
| Mia | $50 |
| name | amount |
|---|---|
| Emma | $40 |
| Emma | $70 |
| Noah | $60 |
| Mia | $50 |
Which rows pass
amount > 30? Tap them in the join.Read the amount on each row and compare it to 30.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Emma 2 Noah 3 Mia orders id customer_id amount 101 1 $35 102 1 $65 103 2 $20 104 2 $45 105 3 $70 SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 106 4 $15 101 1 $25 103 2 $35 105 4 $40 102 2 $50 104 3 $75
Why does WHERE remove the NULL rows of a LEFT JOIN?
The customer whose id is 7 has no orders: their row has a NULL amount. NULL > 0 is not true, so WHERE amount > 0 drops that row.
Let’s take three customers, one of them with no orders.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $40 |
| 102 | 3 | $60 |
| name | amount |
|---|---|
| Emma | $40 |
| Noah | NULL |
| Mia | $60 |
| name | amount |
|---|---|
| Emma | $40 |
| Mia | $60 |
Which rows pass
amount > 30?Compare each amount to 30. On Noah’s row, you compare NULL to 30.
Without
WHERE, which join returns these same rows?
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 1 $45 102 3 $20 103 3 $65 104 4 $35 SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Emma 2 Noah 3 Mia 4 Liam 5 Ava orders id customer_id amount 104 4 $15 106 5 $25 102 2 $30 101 1 $50 105 5 $60 103 2 $70
How do you filter a LEFT JOIN without losing customers?
Written in ON, the condition picks the orders to match, then LEFT JOIN keeps every customer. The customer whose id is 7 has a single order of $12: AND amount > 30 rejects it, and that customer still comes out, with a NULL amount.
Let’s take three customers, with the condition in ON.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 30
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $40 |
| 102 | 1 | $60 |
| 103 | 1 | $25 |
| 104 | 3 | $20 |
| name | amount |
|---|---|
| Emma | $40 |
| Emma | $60 |
| Noah | NULL |
| Mia | NULL |
What’s in the
amountcolumn on Mia’s row?First check which of Mia’s orders pass
amount > 30.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 40
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 1 $50 102 1 $65 103 2 $15 104 2 $45 105 3 $20 106 4 $35 SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 50
customers id name 1 Emma 2 Noah 3 Mia 4 Liam 5 Ava orders id customer_id amount 104 3 $15 101 1 $25 106 5 $30 105 4 $45 102 3 $55 103 3 $70
What is the difference between a condition in ON and in WHERE?
With 7 customers, the condition written in ON gives at least 7 rows, at least one per customer. Written in WHERE, it keeps only the rows where it is true, and may leave none.
Let’s take four customers and both versions of the query.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 30
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| 4 | Liam |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $70 |
| 102 | 1 | $45 |
| 103 | 1 | $20 |
| 104 | 2 | $35 |
| 105 | 4 | $15 |
| name | amount |
|---|---|
| Emma | $70 |
| Emma | $45 |
| Noah | $35 |
| name | amount |
|---|---|
| Emma | $70 |
| Emma | $45 |
| Noah | $35 |
| Mia | NULL |
| Liam | NULL |
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 30
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 30
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| 4 | Liam |
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | $25 |
| 102 | 1 | $55 |
| 103 | 3 | $40 |
| 104 | 3 | $65 |
| 105 | 3 | $50 |
| 106 | 4 | $20 |
How do you quickly check a filtered LEFT JOIN?
First look at where the condition is written. In WHERE, count the orders that pass it. In ON, each customer comes out once per order that passes it, and once if they have none.
SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Emma 2 Noah 3 Mia orders id customer_id amount 101 1 $35 102 3 $60 103 3 $15 SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 40
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 1 $45 102 1 $20 103 3 $30 104 4 $55 SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.amount > 40
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id amount 101 1 $20 105 4 $30 103 2 $35 104 4 $65 102 1 $75 SELECT name, amount FROM customers LEFT JOIN orders ON orders.customer_id = customers.id AND orders.amount > 40
customers id name 1 Emma 2 Noah 3 Mia 4 Liam 5 Ava orders id customer_id amount 104 3 $15 103 3 $25 107 5 $35 105 4 $45 102 1 $50 101 1 $65 106 5 $80