Joins, as sets and as loops
A join is a nested loop with a condition. Every join type is a rule about what to do with the rows that found no partner.
12 min · runs against the shop dataset
Two ways to read a join, and you need both. As sets: pair every row on the left with every row on the right, then keep the pairs the condition likes. As loops: for each row on the left, walk the right looking for matches. The set reading tells you what the answer is; the loop reading tells you what it costs.
run it
| nametext | idinteger | totalnumeric(10,2) |
|---|---|---|
| alan | 12 | 1894.19 |
| alan | 24 | 1417.04 |
| alan | 28 | 710.21 |
| alan | 29 | 1095.15 |
| alan | 37 | 1054.21 |
| alan | 39 | 342.88 |
6 rows · 114 examined · 2 pages read
What happens to the rows that found nobody
An inner join drops them. A left join keeps every row from the left and pads the right side with nulls. That padding is the entire difference, and it is why a left join is the tool for “which of these has none of those”.
The anti-join: keep everything, then keep only what stayed unmatched.
| nametext |
|---|
| alan23 |
| alan24 |
| leslie22 |
| margaret21 |
4 rows · 114 examined · 2 pages read
A null key joins to nothing
Join conditions are comparisons, so the same rule applies: a null on either side is unknown, never a match. A row whose foreign key is null will not appear in an inner join at all — not even paired with the nulls on the other side.
And what it cost
The plan tells you which of the two readings the engine took. A hash join builds a table from one side and probes it once with the other. A nested loop walks the inner side again for every outer row — look at loops in the plan below, because that number is what an N+1 looks like from inside the database.
An equality condition, so the planner can hash it.
plan
| node | est | actual | loops | pages | share |
|---|---|---|---|---|---|
| Hash JoinHash Cond: (c.id = o.customer_id)Buckets: 20 Batches: 1 Memory Usage: 12kB | 24 | 903.8x over | 1 | 0 | |
| → Seq Scan on c | 24 | 24 | 1 | 1 | |
| → Hash | 90 | 90 | 1 | 0 | |
| → Seq Scan on o | 90 | 90 | 1 | 1 |
Did it land?
- Customers who never ordered
Return the names of every customer with no orders at all, ordered by name.
- What each city spent
Return each city and the total of its orders, biggest spender first, then by city name. Call the second column spent. Cities with no orders are not wanted.