Data · SQL

Why WHERE turns a LEFT JOIN into an INNER JOIN

Published

Four orders of $40, $60, $20 and $50: how many pass amount > 30?

From a filter on amounts to two versions of the same query, in five steps:

  1. Filter the rows of a join with WHERE
  2. Follow the NULL row to the WHERE
  3. Keep every customer with a condition in ON
  4. Count the rows of both versions
  5. Practice

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
customers
idname
1Emma
2Noah
3Mia
orders
idcustomer_idamount
1011$40
1021$70
1032$25
1042$60
1053$50
LEFT JOIN, before WHERE
nameamount
Emma$40
Emma$70
Noah$25
Noah$60
Mia$50
result
nameamount
Emma$40
Emma$70
Noah$60
Mia$50
  1. Each customer comes out once per order, as in any join.

    rows

  2. Which rows pass amount > 30? Tap them in the join.

    Read the amount on each row and compare it to 30.

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Emma
    2Noah
    3Mia
    orders
    idcustomer_idamount
    1011$35
    1021$65
    1032$20
    1042$45
    1053$70

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
customers
idname
1Emma
2Noah
3Mia
orders
idcustomer_idamount
1011$40
1023$60
LEFT JOIN, before WHERE
nameamount
Emma$40
NoahNULL
Mia$60
result
nameamount
Emma$40
Mia$60
  1. LEFT JOIN keeps every customer, even with no orders.

    rows

  2. Which rows pass amount > 30?

    Compare each amount to 30. On Noah’s row, you compare NULL to 30.

  3. Without WHERE, which join returns these same rows?

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Emma
    2Noah
    3Mia
    4Liam
    orders
    idcustomer_idamount
    1011$45
    1023$20
    1033$65
    1044$35

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
customers
idname
1Emma
2Noah
3Mia
orders
idcustomer_idamount
1011$40
1021$60
1031$25
1043$20
result
nameamount
Emma$40
Emma$60
NoahNULL
MiaNULL
  1. What’s in the amount column on Mia’s row?

    First check which of Mia’s orders pass amount > 30.

  2. Each customer comes out once per matched order, and once if they have none.

    rows

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
      AND orders.amount > 40
    customers
    idname
    1Emma
    2Noah
    3Mia
    4Liam
    orders
    idcustomer_idamount
    1011$50
    1021$65
    1032$15
    1042$45
    1053$20
    1064$35

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
customers
idname
1Emma
2Noah
3Mia
4Liam
orders
idcustomer_idamount
1011$70
1021$45
1031$20
1042$35
1054$15
result with WHERE
nameamount
Emma$70
Emma$45
Noah$35
result with ON
nameamount
Emma$70
Emma$45
Noah$35
MiaNULL
LiamNULL
  1. The WHERE checks each row of the LEFT JOIN. Mia’s row has a NULL amount.

    rows

  2. Each customer comes out once per order that passes amount > 30, and once if they have none.

    rows

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
customers
idname
1Emma
2Noah
3Mia
4Liam
orders
idcustomer_idamount
1011$25
1021$55
1033$40
1043$65
1053$50
1064$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.

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Emma
    2Noah
    3Mia
    orders
    idcustomer_idamount
    1011$35
    1023$60
    1033$15