Outer joins

Keep every row, even the ones with nothing to match.

6 min read

Overview

An inner join keeps the rows that match, and this guide covers what happens to the rows that do not.

What should happen to a row that has no match?

Take the same customers and orders. Ana and Ben each placed an order and 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.

customers

id	name
1	Ana
2	Ben
3	Cara

orders

id	customer_id	total
101	1	40
102	2	60

Cara has no order. Before reading on, decide whether a query that must list every customer should keep her and what should fill her order columns.

How it works

The table written first is the left side. The demo runs the same data under left and inner semantics, so follow Cara and watch her order columns.

Press play and verify both parts of your prediction by toggling left and inner.

Keep or drop the unmatchedPress play, or step through it yourself.

select c.name, o.id, o.total
from customers c
left join orders o on o.customer_id = c.id;

customers c

id	name
1	Ana
2	Ben
3	Cara

orders o

id	customer_id	total
101	1	40
102	2	60

result

nameidtotal

o.customer_id = c.id

Under the left join Cara’s customer row survives and her order columns are null, because no order row exists to supply them. Under the inner join she disappears because only matched pairs survive.

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 write the table you must keep on the left and leave it there.

Patterns

Three shapes carry most outer-join work, in roughly the order you reach for them.

1
Keep everyone, count what they have
Every customer with their order count, zeros included. Count the order key rather than 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;
```
2
Find the rows with no match
Keep every customer, then keep only the ones whose match came back null. That is every customer who never ordered. The shape is common enough to have its own name, the anti-join, and its own guide.
```
select c.name
from customers c
left join orders o on o.customer_id = c.id
where o.id is null;
```
3
Keep both sides at once
A full join keeps the unmatched rows from both tables together: customers with no order and orders with no customer. It is the rarest of the three, worth reaching for only when both absences matter.

Trade-offs

The choice between inner and outer is a choice about what a missing match means. If an unmatched row is noise, an inner join is right and smaller. If the unmatched row is the answer, such as a customer who never ordered, only an outer join can show it. Learning to make that call is most of what this concept asks, because the syntax difference is one word.

Among the outer joins, prefer left and write the preserved table first. A right join reads backwards for no gain, and a full join is useful only when unmatched rows from both sides belong in the answer.

Pitfalls

Every trap here comes from the same source: the null that an unmatched row carries.

A filter in WHERE turns a left join back into an inner join

Add where o.total > 50 and Cara vanishes, because her o.total is null and null is not greater than 50. The left join has quietly become an inner join. When you want to filter the right table but keep the unmatched rows, put the condition in the on clause, where it shapes the match. A condition in where shapes the result instead.

Count the key, not the rows. count(o.id) skips the null and gives Cara zero, while count(*) counts her null row as one. The two answer different questions, so 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, not because they fail the test. Reach for is null when absence is the question.
Write the kept table on the left. A right join preserves the right table instead. It works, but a query that keeps its far side is needless effort to read.

Performance

An outer join performs the matching work and then retains unmatched rows from the preserved side. Check the relationship key, fan-out, and downstream filters because the word left is not itself the main cost.

Practice

EasyReturn every member with a May checkout count, keeping the date predicate in on and counting checkout_id. A member with no May activity must remain with zero.MediumBuild a customer anti-list with left join ... is null and include both ordered and never-ordered test rows. Only customers with zero matching orders should survive.HardRefactor a recent-orders report so every customer remains by prefiltering the right side or filtering in on. Confirm that customers with no recent orders still appear exactly once.

Recap

Put the table that must survive on the left, then use a left join to retain all of its rows.
Expect nulls where a match is absent and decide what those nulls mean before filtering or counting.
Place a right-side filter in on when unmatched rows must remain. Use where when they should be removed.
Count a non-null match key when absence should produce zero. count(*) counts the preserved row.
Use inner when unmatched rows are irrelevant, or full when absences from both sides matter.