Keeping the unmatched rows
An inner join drops rows with no partner. Sometimes those rows are exactly what you want: customers who never bought anything, products nobody ordered, articles with no comments. LEFT JOIN keeps every row of the left table (the one written before JOIN) and attaches matching rows from the right table. Where there is no match, the right table's columns are NULL.
SELECT c.id, c.first_name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (1, 9, 12)
ORDER BY c.id, o.id;+----+------------+----------+-----------+
| id | first_name | order_id | status |
+----+------------+----------+-----------+
| 1 | Asha | 1 | delivered |
| 1 | Asha | 2 | delivered |
| 1 | Asha | 11 | delivered |
| 1 | Asha | 20 | shipped |
| 9 | Isabel | NULL | NULL |
| 12 | Liam | NULL | NULL |
+----+------------+----------+-----------+Customer 1 appears once per order. Customers 9 and 12 appear once each, with NULL for the order columns. An inner join would have removed them.
Finding rows with no match
The classic trick: LEFT JOIN, then keep only rows where the right side's key is NULL. Those are the left rows with no partner.
SELECT c.id, c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 9 | Isabel | Santos |
| 12 | Liam | O'Brien |
+----+------------+-----------+Products that were never ordered:
SELECT p.id, p.name, p.stock
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL;+----+--------------------+-------+
| id | name | stock |
+----+--------------------+-------+
| 15 | Puzzle 1000 Pieces | 0 |
+----+--------------------+-------+Test the column that cannot be NULL when a match exists, such as the right table's primary key. Testing a nullable column would confuse "no match" with "matched but NULL".
Counting with LEFT JOIN
Orders per customer, including customers with none:
SELECT c.first_name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.first_name
ORDER BY orders DESC, c.first_name;+------------+--------+
| first_name | orders |
+------------+--------+
| Asha | 4 |
| Farid | 3 |
| Ben | 2 |
| Chloe | 2 |
| Daniel | 2 |
| Hiro | 2 |
| Jack | 2 |
| Elena | 1 |
| Grace | 1 |
| Kavya | 1 |
| Isabel | 0 |
| Liam | 0 |
+------------+--------+COUNT(o.id) counts only real orders, because it ignores NULLs, so Isabel and Liam get 0. If you wrote COUNT(*), they would get 1, since they still produce one (mostly empty) row each.
ON versus WHERE
This is the subtle part. Suppose you want every customer with the number of their delivered orders. Put the status test in the wrong place and customers vanish:
SELECT c.id, c.first_name, COUNT(o.id) AS delivered
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'delivered'
GROUP BY c.id, c.first_name
ORDER BY c.id;+----+------------+-----------+
| id | first_name | delivered |
+----+------------+-----------+
| 1 | Asha | 3 |
| 2 | Ben | 1 |
| 3 | Chloe | 1 |
| 4 | Daniel | 2 |
| 6 | Farid | 1 |
| 8 | Hiro | 1 |
| 10 | Jack | 1 |
| 11 | Kavya | 1 |
+----+------------+-----------+Customers without delivered orders disappear entirely, because for them o.status is NULL and WHERE removes the row. The WHERE filter ran after the join and undid the outer join. Move the condition into ON, so it decides which orders match, not which customers appear:
SELECT c.id, c.first_name, COUNT(o.id) AS delivered
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'delivered'
GROUP BY c.id, c.first_name
ORDER BY c.id;+----+------------+-----------+
| id | first_name | delivered |
+----+------------+-----------+
| 1 | Asha | 3 |
| 2 | Ben | 1 |
| 3 | Chloe | 1 |
| 4 | Daniel | 2 |
| 5 | Elena | 0 |
| 6 | Farid | 1 |
| 7 | Grace | 0 |
| 8 | Hiro | 1 |
| 9 | Isabel | 0 |
| 10 | Jack | 1 |
| 11 | Kavya | 1 |
| 12 | Liam | 0 |
+----+------------+-----------+Now all twelve customers appear, with 0 where they have no delivered orders. Remember: for the right table of a LEFT JOIN, conditions go in ON. Conditions on the left table can stay in WHERE.
RIGHT JOIN
RIGHT JOIN is the mirror image: it keeps every row of the right table. This query returns the same rows as customers c LEFT JOIN orders o would:
SELECT c.first_name, o.id FROM orders o RIGHT JOIN customers c ON o.customer_id = c.id WHERE c.id IN (9, 12);+------------+------+
| first_name | id |
+------------+------+
| Isabel | NULL |
| Liam | NULL |
+------------+------+Most people avoid RIGHT JOIN and swap the table order to use LEFT JOIN, because reading left to right is easier when everything is in one direction.
Full outer join
A full outer join keeps unmatched rows from both sides. MySQL has no FULL JOIN keyword. The usual workaround combines a left and a right join with UNION, which the union lesson shows.
Common mistakes
- Using
COUNT(*)and getting 1 for unmatched rows. - Filtering the right table in WHERE, which silently turns the join into an inner join.
- Testing a nullable column for "no match".
- Choosing the wrong side. If you want all customers, customers must be on the left of a
LEFT JOIN. - Mixing RIGHT and LEFT joins in one query, which gets hard to read.
Exercise
List every product category with the number of products in it that have been ordered at least once. Include categories with zero. (Hint: you need two joins.) Then list the employees who have no department.
Show one possible answer
SELECT cat.name AS category, COUNT(DISTINCT p.id) AS ordered_products
FROM categories cat
LEFT JOIN products p ON p.category_id = cat.id
AND EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id)
GROUP BY cat.id, cat.name
ORDER BY cat.id;
SELECT e.name FROM employees e LEFT JOIN departments d ON d.id = e.department_id WHERE d.id IS NULL;+-------------+------------------+
| category | ordered_products |
+-------------+------------------+
| Electronics | 4 |
| Books | 3 |
| Home | 3 |
| Sports | 3 |
| Toys | 1 |
+-------------+------------------+
+-------------+
| name |
+-------------+
| Victor Lang |
+-------------+Toys has product 14 (ordered) and product 15 (never ordered), so it counts 1. Victor is the employee with no department.
Key points
- LEFT JOIN keeps every row from the left table and fills the right table's columns with NULL when there is no match.
- Find rows with no related rows by LEFT JOINing and filtering WHERE right_table.key IS NULL.
- Use COUNT(right.column), not COUNT(*), when counting related rows, otherwise unmatched rows count as 1.
- A filter on the right table belongs in ON for a LEFT JOIN. Putting it in WHERE turns the query back into an inner join.