CROSS JOIN and Cartesian Products
Deliberate cross joins for generating combinations and accidental ones that explode row counts.
The Join Interviewers Use to Trap You
A CROSS JOIN pairs every row of the left table with every row of the right table. There is no ON condition. If table A has 4 rows and table B has 3 rows, the result has 4 x 3 = 12 rows.
This is called a Cartesian product. Interviewers ask about it for two reasons: to test whether you can generate combinations on purpose, and to see if you recognize an accidental cross join that explodes a result set.
Explicit CROSS JOIN Syntax
The modern, readable way to write a Cartesian product is the explicit CROSS JOIN keyword. Notice there is no ON clause, which signals intent clearly to a reviewer.
Here we pair every size with every color to build a full product matrix.
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;All lessons in this course
- CROSS JOIN and Cartesian Products
- SELF JOIN for Hierarchies
- Comparing Rows Within One Table
- Choosing the Right Join Type