LEFT JOIN

Lesson 12 of 21 · Part 3 · More than one table · about 15 minutes

The JOIN from the last lesson keeps only the rows that match on both sides. But plenty of questions are about the rows that don't match: which customers have never ordered? Which product has nobody bought? This lesson is about LEFT JOIN, which keeps every row of the left table.

The unmatched rows are gone

First, join customers and orders the way the last lesson did:

1SELECT c.id, c.name, o.id AS order_id2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4ORDER BY c.id, o.id;

The result has 12 rows, one per order. There are 10 customers, but customers 6, 9 and 10 are missing: they have never ordered, so nothing in orders matches them, and JOIN drops them.

LEFT JOIN

Change JOIN to LEFT JOIN and nothing else:

1SELECT c.id, c.name, o.id AS order_id2FROM customers AS c3LEFT JOIN orders AS o ON o.customer_id = c.id4ORDER BY c.id, o.id;

Now there are 15 rows. Customers 6, 9 and 10 are back, and their order_id is NULL.

LEFT JOIN means: keep every row of the table written after FROM (the left table). A row that matches is joined to the right table's rows just as in the last lesson. A row that doesn't match still gets one row, with every column from the right table filled with NULL.

Step through the NULLs

Press Step on the query above and look at the JOIN stage: each customer looks in orders for the orders whose customer_id equals their id. Customer 1, Ana Silva, finds 3 and becomes 3 rows; when it gets to customer 6, Farah Khan, nothing matches, yet she still keeps one row, and every cell on the right is NULL.

The right side has more columns than id. Pick every column of the order to see it more clearly (o.* means all of o's columns):

1SELECT c.name, o.*2FROM customers AS c3LEFT JOIN orders AS o ON o.customer_id = c.id4WHERE c.id >= 85ORDER BY c.id;

Hana Sato has one order, and all four columns have values; Ivan Petrov and Julia Costa have no orders, so all four orders columns are NULL.

Customers who never ordered

Since an unmatched row has NULL on the right, keeping only those rows gives "customers who never ordered":

1SELECT c.id, c.name2FROM customers AS c3LEFT JOIN orders AS o ON o.customer_id = c.id4WHERE o.id IS NULL5ORDER BY c.id;

WHERE runs after the JOIN. By then, the rows for customers 6, 9 and 10 have NULL in o.id, and every other row has an order number. Remember to write IS NULL: = NULL finds nothing at all (lesson 5).

1SELECT p.name2FROM products AS p3LEFT JOIN order_items AS oi ON oi.product_id = p.id4WHERE oi.order_id IS NULL;

Orders per customer

Add GROUP BY to a LEFT JOIN and you can count each customer's orders, zeros included:

1SELECT c.id, c.name, COUNT(o.id) AS order_count2FROM customers AS c3LEFT JOIN orders AS o ON o.customer_id = c.id4GROUP BY c.id, c.name5ORDER BY c.id;

It has to be COUNT(o.id), not COUNT(*). Farah Khan's group has a single row, the one padded with NULL: COUNT(*) counts rows and gives 1; COUNT(o.id) skips NULL and gives 0. Change it to COUNT(*) and run it to see.

Put the condition in ON

Say you want each customer's pending orders, with customers who have none still listed. The obvious move is a condition in WHERE, but that loses the customers again:

1SELECT c.name, o.id AS order_id2FROM customers AS c3LEFT JOIN orders AS o ON o.customer_id = c.id4WHERE o.status = 'pending'5ORDER BY c.id;

Only 3 rows are left. In a padded row, o.status is NULL too, so it fails the WHERE. Move the condition into ON and it becomes part of the matching: only pending orders are matched, and customers with no match still stay:

1SELECT c.name, o.id AS order_id2FROM customers AS c3LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'pending'4ORDER BY c.id;

Tip: With LEFT JOIN, put conditions on the right table in ON. Put them in WHERE and your LEFT JOIN behaves much like a plain JOIN.

RIGHT JOIN

RIGHT JOIN is the other way round: every row of the right table stays. orders RIGHT JOIN customers gives the same rows as customers LEFT JOIN orders. Most people only use LEFT JOIN and write the table they want to keep whole right after FROM; it reads more naturally.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

1SELECT p.id, p.name2FROM products AS p3JOIN order_items AS oi ON oi.product_id = p.id;

1SELECT p.name, SUM(oi.quantity) AS sold2FROM products AS p3LEFT JOIN order_items AS oi ON oi.product_id = p.id4GROUP BY p.id, p.name5ORDER BY p.id;

1SELECT c.name, o.id AS order_id2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4WHERE c.city = 'Paris'5ORDER BY c.id, o.id;

Here the condition is fine in WHERE: c.city is a column of the left table, and only the right side gets padded with NULL.

What you learned

• JOIN keeps only matching rows; LEFT JOIN keeps the whole left table and pads unmatched rows with NULL on the right.
• LEFT JOIN … WHERE right.id IS NULL finds rows with no match: customers who never ordered, products nobody bought.
• When counting with GROUP BY, use COUNT(a right column); COUNT(*) counts the padded row as 1.
• Conditions on the right table go in ON; in WHERE they delete the padded rows again.
• RIGHT JOIN keeps the right table; usually you swap the order and write LEFT JOIN.