LEFT JOIN: Keeping Unmatched Rows

An inner join drops rows with no match. A LEFT JOIN (or LEFT OUTER JOIN) keeps every row of the left table, filling the right table's columns with NULL where there's no match.

SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

+-------------------+----------+-----------+
|       name        | order_id |  status   |
+-------------------+----------+-----------+
| Ada Lovelace      |        1 | delivered |
| Ada Lovelace      |        2 | delivered |
| Ada Lovelace      |        8 | pending   |
| Linus Torvalds    |        3 | shipped   |
| Grace Hopper      |        4 | delivered |
| Grace Hopper      |        5 | cancelled |
| Alan Turing       |        6 | pending   |
| Margaret Hamilton |        7 | shipped   |
| Tim Berners-Lee   | NULL     | NULL      |
+-------------------+----------+-----------+

Tim Berners-Lee now appears, with NULL for the order columns — he has no orders.

Finding rows with no match

A very common pattern: LEFT JOIN, then keep only the rows where the right side is NULL:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

+-----------------+
|      name       |
+-----------------+
| Tim Berners-Lee |
+-----------------+

"Customers who have never ordered." The same pattern finds products never sold, users who never logged in, and so on.

Counting, including zeros

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY orders DESC, c.name;

+-------------------+--------+
|       name        | orders |
+-------------------+--------+
| Ada Lovelace      |      3 |
| Grace Hopper      |      2 |
| Alan Turing       |      1 |
| Linus Torvalds    |      1 |
| Margaret Hamilton |      1 |
| Tim Berners-Lee   |      0 |
+-------------------+--------+

COUNT(o.id) counts only matched orders, so Tim gets 0. COUNT(*) would count his row and wrongly give 1.

Careful with WHERE after a LEFT JOIN

A condition on the right table in WHERE removes the NULL rows, silently turning the left join back into an inner join:

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'pending'
ORDER BY c.id;

+-------------------+----------+
|       name        | order_id |
+-------------------+----------+
| Ada Lovelace      |        8 |
| Linus Torvalds    | NULL     |
| Grace Hopper      | NULL     |
| Alan Turing       |        6 |
| Margaret Hamilton | NULL     |
| Tim Berners-Lee   | NULL     |
+-------------------+----------+

Putting o.status = 'pending' in the ON clause keeps every customer, showing a pending order where one exists. Writing it in WHERE instead would keep only customers who have a pending order.

Practice

List every product with the number of times it has been ordered (including products never ordered).

SELECT p.name, COUNT(oi.order_id) AS times_ordered
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY times_ordered DESC, p.name;

+-----------------------------+---------------+
|            name             | times_ordered |
+-----------------------------+---------------+
| 27-inch Monitor             |             2 |
| Laptop Stand                |             2 |
| Mechanical Keyboard         |             2 |
| Noise-Cancelling Headphones |             2 |
| USB-C Hub                   |             2 |
| Webcam                      |             2 |
+-----------------------------+---------------+