Cross join

Every combination of two tables, on purpose.

5 min read

Overview

Every join so far has matched rows. The cross join is the one that does not: it pairs every row of one table with every row of the other, no condition, no key. When it happens by accident it is a bug, and when you ask for it on purpose it is how you build a grid.

sizes

size
S
M

colors

color
Red
Blue

A shop sells shirts in two sizes and two colors. List the combinations and predict how many catalog rows the two tables create.

How it works

With no condition to satisfy, there is nothing to match and nothing to drop. Each row on the left pairs with every row on the right. Keep your combination list and predicted count beside the query. The demo will let you verify both.

select s.size, c.color
from sizes s
cross join colors c
order by s.size, c.color;

Press play and check your list and row count.

Every pair, on purposePress play, or step through it yourself.2 × 2 =

sizes s

size
S
M

colors c

color
Red
Blue

result

sizecolor

no condition: every row pairs

The result has four rows because each of the two sizes pairs with each of the two colors. Nothing is filtered or matched by a key, so the output count is the product of the input counts.

You will also meet the older spelling, a bare comma: from sizes s, colors c. The comma form is the same cross join without the explicit keyword, and most accidental products in old code start exactly there.

Patterns

A deliberate cross join is almost always building a scaffold.

1
Generate a grid
Every variant a catalog should carry, every test case a matrix should cover. Crossing the option tables produces the grid directly, one row per combination.
2
Scaffold first, then find the gaps
Every store crossed with every day gives the rows that SHOULD exist. Left join actual sales onto that scaffold and the null rows are the days a store sold nothing, rows no plain join could show you because they are not in the data. The scaffold pairs naturally with the outer join.
```
select st.name, d.day, s.total
from stores st
cross join days d
left join sales s
  on s.store_id = st.id and s.day = d.day;
```
3
Attach a constant row
Cross join a one-row table of parameters and every row gets those values attached. A computed date works the same way, since one row times n rows is still n rows and nothing multiplies.

Trade-offs

The real choice is between combination and matching rather than between join types. If a key relates the tables, join on it. If the point is every pairing, write cross join explicitly rather than a bare comma. The keyword tells the next reader the product is intended, and old-style comma lists are where forgotten conditions hide. Keep the sides small on purpose, because the product grows faster than intuition expects.

Pitfalls

The accidental product

Forget the on clause, or list tables with commas and forget the where, and every row silently pairs with every row. Three orders and three customers become nine rows, which still looks plausible. A thousand of each becomes a million, and the sums come out wrong while still looking believable. When a result has more rows than the tables that fed it, suspect an unintended cross join before anything else.

The product grows fast. A thousand rows on each side is a million pairs, and a million on each side is a trillion, which no plan can make affordable. Know both row counts before you multiply them.
The comma spelling hides intent. The reader of from a, b cannot tell a deliberate product from a forgotten condition. Write cross join when you mean it.
Duplicates multiply too. A scaffold built from tables with repeated rows repeats its combinations. Feed the cross join distinct sides.

Performance

A cross join must emit n times m rows. Multiply the input counts first, then reduce or deduplicate the inputs when the product is too large.

Practice

EasyGenerate every sizes and colors combination with an explicit cross join. Confirm that the result contains exactly size_count times color_count unique pairs.MediumCreate a store-day scaffold for a fixed date range and left join daily sales without dropping empty cells. Each store should have one row per day with zero for no sales.HardRepair a report whose totals tripled by restoring the missing join condition. Compare row counts before and after. Each fact must match only its intended dimension row.

Recap

Reserve a cross join for cases where every combination is the intended result.
Predict n times m rows before running it and reduce the inputs when that product is unsafe.
Write cross join explicitly so a reviewer can distinguish intent from a missing condition.
Check each input for duplicates because repeated values multiply repeated combinations.
Return to a keyed join when a relationship key exists, and suspect a missing condition when rows explode.