Joins
Filtering joined data: ON vs WHERE
Let's first add three customers for this lesson:
12345INSERT INTO customers (id, email, full_name, country, created_at)VALUES (10001, 'Customer10001@Example.com', 'Customer 10001', 'US', '2025-01-01'), (10002, 'Customer10002@Example.com', 'Customer 10002', 'US', '2025-01-01'), (10003, 'Customer10003@Example.com', 'Customer 10003', 'US', '2025-01-01');Now add two orders:
12345678910111213141516171819202122232425262728INSERT INTO orders ( id, customer_id, status, total_amount, payment_reference, placed_at, shipped_at)VALUES ( 900001, 10001, 'processing', 49.99, 'pay_on_where_1', '2025-01-02', NULL ), ( 900002, 10002, 'delivered', 79.99, 'pay_on_where_2', '2025-01-02', '2025-01-04' );We now have:
Customer 10001, who has aprocessingorderCustomer 10002, who has adeliveredorderCustomer 10003, who has no order
Now suppose we want to list all three customers and show their order only if it's still being processed.
Run this query:
1234567891011SELECT c.id AS customer_id, c.full_name, o.id AS order_id, o.statusFROM customers cLEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'processing'WHERE c.id IN (10001, 10002, 10003)ORDER BY c.id;You should see:
12345 customer_id | full_name | order_id | status-------------+----------------+----------+------------ 10001 | Customer 10001 | 900001 | processing 10002 | Customer 10002 | NULL | NULL 10003 | Customer 10003 | NULL | NULLAll three customers are still returned, but only Customer 10001 has a matching processing order in the result.
Now, I want you to focus on this part of the join condition:
12ON c.id = o.customer_idAND o.status = 'processing'Previously, the join condition only checked whether an order belonged to a customer with ON c.id = o.customer_id.
This time, we're asking PostgreSQL to check two things before an order counts as a match:
12ON c.id = o.customer_idAND o.status = 'processing'The order must belong to the customer and its status must be processing.
For Customer 10001, PostgreSQL finds an order whose customer_id is 10001 and whose status is processing. The order therefore counts as a match.
Customer 10002 also has an order, but its status is delivered. It doesn't satisfy the complete join condition, so PostgreSQL treats it as if no matching order was found. Because this is a LEFT JOIN, the customer is still returned, but the columns from orders contain NULL.
Customer 10003 doesn't have an order at all. Because this is a LEFT JOIN, the customer is still returned with NULL values for the columns from orders.
The condition in ON therefore controls which rows from `orders` are allowed to match each customer. It doesn't remove customers that don't have a matching row.
But what if we don't want to keep all three customers? What if we want the result to contain only customers whose order is currently being processed?
Putting the condition in WHERE
Move the status condition from the join condition to the WHERE clause:
1234567891011SELECT c.id AS customer_id, c.full_name, o.id AS order_id, o.statusFROM customers cLEFT JOIN orders o ON c.id = o.customer_idWHERE c.id IN (10001, 10002, 10003) AND o.status = 'processing'ORDER BY c.id;This time you should see only:
123 customer_id | full_name | order_id | status-------------+----------------+----------+------------ 10001 | Customer 10001 | 900001 | processingWhy did the other two customers disappear?
This time, the join condition is only ON c.id = o.customer_id, so PostgreSQL first joins customers with their orders based on customer_id.
For Customer 10001, the joined row contains Customer 10001 | 900001 | processing.
For Customer 10002, the joined row contains Customer 10002 | 900002 | delivered.
For Customer 10003, no matching order exists, so the order columns contain NULL and the joined row is Customer 10003 | NULL | NULL.
The WHERE clause then filters those result rows with AND o.status = 'processing'.
For Customer 10001, o.status is processing, so the row is kept.
For Customer 10002, o.status is delivered, so the row is removed.
For Customer 10003, o.status is NULL. NULL = 'processing' isn't true, so that row is removed as well.
The result therefore contains only customers whose joined order has a status of processing.
ON and WHERE answer different questions
Consider these two queries.
This:
1234FROM customers cLEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'processing'means:
Keep the customers, but only match them with processing orders.
While this:
1234FROM customers cLEFT JOIN orders o ON c.id = o.customer_idWHERE o.status = 'processing'means:
After joining the tables, keep only result rows whose order is processing.
That's why moving the same condition from ON to WHERE can change the result of a LEFT JOIN.
A useful way to think about the two clauses is:
ONdetermines which rows from the joined tables match each other.WHEREfilters the rows in the result.
With a LEFT JOIN, this distinction matters because a WHERE condition on a column from the right table can remove the unmatched rows that the LEFT JOIN would otherwise preserve.
Notice that our queries also contain WHERE c.id IN (10001, 10002, 10003). This condition isn't deciding whether a customer matches an order. It simply limits the result to the three customers we're using in this lesson, which is why it belongs in the WHERE clause.
Before continuing, remove the rows added for this lesson:
12345DELETE FROM ordersWHERE id IN (900001, 900002);
DELETE FROM customersWHERE id IN (10001, 10002, 10003);