Cross Join
SQL · Joining Multiple Tables
Theory
CROSS JOIN returns the Cartesian product of two tables: every row of the first is paired with every row of the second, with no join condition. Two sizes crossed with three colours give 2 x 3 = 6 combinations. Writing FROM a, b with no WHERE condition does the same thing in older comma-join syntax.
Row counts multiply quickly: 1,000 rows crossed with 1,000 rows is a million rows, so cross joins on large tables are expensive and often a mistake (for example, a forgotten ON condition). Used on purpose with small tables they are very useful: building every product-variant combination, creating a calendar of every date for every store, or attaching a single-row settings table to every row.
Why we use it
Sometimes you need all possible combinations, not just the ones present in the data, for example every store for every day so that days with zero sales still show up. CROSS JOIN generates that full grid.
When to use it
Use CROSS JOIN deliberately to generate combinations or a reporting grid, then LEFT JOIN actual data onto it. If the tables are related by a key, use INNER or LEFT JOIN with an ON condition instead.
How it works
- Take the first row of table A
- Pair it with every row of table B
- Move to the next row of A
- Rows left in A? Yes: Pair that row with all of B too. No: Stop.
- Result has rows(A) x rows(B) rows
Syntax
SELECT columns
FROM t1
CROSS JOIN t2;Example
SELECT * FROM sizes CROSS JOIN colors;Key points
- No join condition required
- Row count = rows(t1) x rows(t2)
- Comma-separated FROM a, b without WHERE is a cross join
- Great for generating combinations or date-by-store grids
- A forgotten ON condition can create an accidental cross join