Outer joins
Keep the rows that do not match, filling the missing side with null.
Overview
An inner join is about the rows that match. An outer join is about the rows that do not. Take the same customers and orders: Ana and Ben each placed an order, Cara placed none. To list every customer and their orders, including Cara, an inner join is no help. It drops her, because she has nothing to match.
| id | name |
|---|---|
| 1 | Ana |
| 2 | Ben |
| 3 | Cara |
| id | customer_id | total |
|---|---|---|
| 101 | 1 | 40 |
| 102 | 2 | 60 |
A left join keeps every customer, matched or not. Cara comes back too, with null where her order would be. Toggle the join type below and watch her appear and disappear.
How it works
A left join keeps every row of the left table, the one you write first. Rows that find a match are filled in from the right table as usual. Rows that find no match are kept anyway, with null in every column that would have come from the right. Cara has no order, so her o.id and o.total are null.
A right join is the same idea with the sides swapped, and a full join keeps the unmatched rows from both tables. Most of the time you want a left join, so put the table you must keep on the left.
select c.name, o.id, o.total from customers c left join orders o on o.customer_id = c.id;
| id | name |
|---|---|
| 1 | Ana |
| 2 | Ben |
| 3 | Cara |
| id | customer_id | total |
|---|---|---|
| 101 | 1 | 40 |
| 102 | 2 | 60 |
Patterns
Outer joins earn their place whenever a missing row is part of the answer.
Every customer with their order count, zeros included. Count the order key, not the rows, so a customer with no order counts zero rather than one.
select c.name, count(o.id) as orders
from customers c
left join orders o on o.customer_id = c.id
group by c.name;Keep every customer, then keep only the ones whose match came back null. That is every customer who never ordered. This shape is common enough to have its own name, the anti-join, which has its own guide.
select c.name
from customers c
left join orders o on o.customer_id = c.id
where o.id is null;A full join keeps the unmatched rows from both tables at once: customers with no order and orders with no customer. It is rarer, and worth reaching for only when both absences matter.
Trade-offs
Use an inner join when you only want matches, and a left join when a missing match is part of the answer. Prefer left over right and write the preserved table first; a right join reads backwards for no gain. Reach for a full join only when the unmatched rows on both sides matter.
One dialect note: full outer join is native in most databases, but MySQL does not support it directly. There you build it from a left join and a right join combined with union.
Pitfalls
Add where o.total > 50 and Cara vanishes, because her o.total is null and null is not greater than 50. The left join quietly became an inner join. When you want to filter the right table but keep the unmatched rows, put the condition in the on clause, not where.
- Count the key, not the rows. count(o.id) skips the null and gives Cara zero. count(*) counts her null row as one. Pick the one you mean.
- Null is not a value to compare. Comparing an unmatched row to a value is never true, because its columns are null. Test with is null when absence is the question.
- Write the kept table on the left. A right join keeps the right table instead. It works, but reading a query that preserves the far side is needless effort.
Performance
An outer join costs about the same as an inner join, so index the columns you join on. The extra work is only the unmatched rows it keeps, which the database would have found either way.
Practice
Recap
- A left join keeps every row of the left table; unmatched rows get null from the right.
- Use it when a missing match is part of the answer, like a customer with no order.
- Filter the right table in the on clause, not where, or the left join collapses to inner.
- count(o.id) gives zero for an unmatched row; count(*) gives one.
- Prefer left over right; write the table you must keep first.