Joins
Keeping unmatched rows with LEFT JOIN
In the previous chapter, you used an INNER JOIN to combine orders with their customers. You learnt that an INNER JOIN returns only rows that have a match in both tables.
Sometimes, however, we want to keep rows even when no matching row exists in the other table.
For example, imagine we want to list customers along with their orders, including customers who haven't placed an order yet.
To make this easy to see, first add two customers for this lesson:
1234INSERT 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');Now add an order for Customer 10001:
123456789101112131415161718INSERT INTO orders ( id, customer_id, status, total_amount, payment_reference, placed_at, shipped_at)VALUES ( 900001, 10001, 'delivered', 49.99, 'pay_left_join_example', '2025-01-02', '2025-01-04');Customer 10001 now has an order, while Customer 10002 does not.
What happens with INNER JOIN?
First, join these customers to orders using an INNER JOIN:
12345678910SELECT c.id AS customer_id, c.full_name, o.id AS order_id, o.total_amountFROM customers cINNER JOIN orders o ON c.id = o.customer_idWHERE c.id IN (10001, 10002)ORDER BY c.id;You should see:
123 customer_id | full_name | order_id | total_amount-------------+----------------+----------+-------------- 10001 | Customer 10001 | 900001 | 49.99Customer 10002 doesn't appear in the result.
That's because an INNER JOIN keeps only rows for which the join condition finds a match in both tables.
There is no row in orders where customer_id = 10002, so that customer is excluded.
Using LEFT JOIN
Now change the query to use a LEFT JOIN:
12345678910SELECT c.id AS customer_id, c.full_name, o.id AS order_id, o.total_amountFROM customers cLEFT JOIN orders o ON c.id = o.customer_idWHERE c.id IN (10001, 10002)ORDER BY c.id;You should now see:
1234 customer_id | full_name | order_id | total_amount-------------+----------------+----------+-------------- 10001 | Customer 10001 | 900001 | 49.99 10002 | Customer 10002 | NULL | NULLThis time, both customers are returned.
The query starts with:
12FROM customers cLEFT JOIN orders oThis makes customers the left table and orders the right table.
A LEFT JOIN keeps every row from the left table.
For Customer 10001, PostgreSQL finds a matching order, so columns from the two rows are combined.
For Customer 10002, PostgreSQL doesn't find a matching order. The customer is still included, but the columns that would have come from orders contain NULL.
A simplified view looks like this:
The order of the tables matters
A LEFT JOIN keeps unmatched rows only from the table on the left.
In our query:
123FROM customers cLEFT JOIN orders o ON c.id = o.customer_idPostgreSQL starts with the rows from customers and looks for matching rows in orders.
That's why Customer 10002 is still returned even though no matching order exists.
If we reverse the tables:
123FROM orders oLEFT JOIN customers c ON o.customer_id = c.idthe meaning changes.
PostgreSQL now starts with the rows from orders and looks for matching customers. It preserves orders that don't have a matching customer, but it doesn't preserve customers that don't have an order.
So when writing a LEFT JOIN, ask: Which table contains the rows I want to keep even when there is no match?
Put that table on the left.
A common use for LEFT JOIN
LEFT JOIN is useful when the absence of related data is itself important.
For example, we can find customers who haven't placed any orders by keeping all customers and then looking for rows where no matching order was found:
12345678SELECT c.id, c.full_nameFROM customers cLEFT JOIN orders o ON c.id = o.customer_idWHERE o.id IS NULL AND c.id IN (10001, 10002);You should see:
123 id | full_name-------+---------------- 10002 | Customer 10002Because Customer 10002 has no matching order, o.id is NULL.
This pattern is commonly used to find rows that don't have a related row in another table.
Before continuing, remove the rows added for this lesson:
12345DELETE FROM ordersWHERE id = 900001;
DELETE FROM customersWHERE id IN (10001, 10002);