Premium

CROSS JOIN

Basic SQL
≈ 1 min read

CROSS JOIN creates every possible pair of rows

CROSS JOIN does not look for matches by key. It combines every row from the first set with every row from the second. 

SELECT c.name, s.size
FROM colors AS c
CROSS JOIN sizes AS s;

If colors contains 3 rows and sizes contains 4, the result contains 12 combinations.

When a Cartesian product is useful

This kind of join is useful when every combination is meaningful: for example, when building a “plan × region” matrix or a set of “color × size” variants.

The result size grows very quickly. With 10,000 rows in one table and 10,000 in another, a CROSS JOIN can potentially produce 100 million combinations. Use it only when a Cartesian product is actually required.
Back Next