Self joins and cross joins

Join a table to itself, ask for every combination on purpose, and recognise the one you did by accident.

10of 22
≈ 11Min read
5Parts

A table joined to itself

Nothing in join says the two tables have to be different. Give the same table two aliases and it becomes two tables: a and b. This is how you compare rows with other rows — pairs, duplicates, a hierarchy, a previous row.

doors made of the same material
select a.name as one, b.name as other, a.material
from   doors a
join   doors b on b.material = a.material
             and b.id > a.id            -- the important line
order by a.material, a.id;
b.id > a.id, not b.id <> a.id

Without any condition each door pairs with itself. With <> you get every pair twice, in both directions. With > you get each pair once — eight of them here. The same trick de-duplicates any symmetric relationship.

A hierarchy in one table

A self join reaches exactly one level. Two levels need two joins; an unknown number of levels needs recursion, which is the with recursive in the CTE lesson.

the shape you will meet in every staff table
-- employees(id, name, manager_id → employees.id)
select   e.name as person, m.name as manager
from     employees e
left join employees m on m.id = e.manager_id;    -- left: the boss has no manager

cross join: every combination

A deliberate cross join is how you build a grid to compare against reality: every month × every fitter, every size × every colour. Left join the real data onto it and the gaps appear as NULLs — a report that shows the weeks with no work, instead of skipping them.

four styles × four materials = sixteen possibilities
select s.style, m.material
from   (select distinct style from doors) s
cross join (select distinct material from doors) m;     -- 16 rows

-- only nine of those sixteen are things we actually make

The Cartesian product you did not mean

The comma is the old join syntax and it has no on. In a three-table query written with commas, one forgotten condition in the where clause multiplies the result — the query still runs, the numbers are simply nonsense. Modern join … on makes the mistake hard to write, which is the whole reason it exists.

a missing on clause, and 120 rows out of 22
select * from customers, doors;                 -- 12 × 10 = 120
select * from customers c join doors d on 1 = 1;  -- the same thing, said out loud
A sanity check worth the second

Count the rows before and after adding a join. If joining a "many" table changed a count you meant to keep, you are now counting lines, not orders — sum a column and you are double-counting.

Ranges, not equality

Equality is the common case, not the rule. Band tables, date ranges and "the nearest one below" are all ordinary joins with an inequality in the on clause.

a join condition is any boolean expression
-- which price band does each door fall into?
select d.name, d.price, b.label
from   doors d
join   bands b on d.price >= b.low and d.price < b.high;

-- jobs that ran on the day an order was placed
select o.id, j.day
from   orders o
join   jobs j on j.day = o.placed;

Practice

Next →Subqueries