What is a CROSS JOIN and when would you use it?
A CROSS JOIN pairs every row of one table with every row of the other, giving rows1 x rows2 results. It is useful for generating combinations, such as every size with every colour.
Explain it simply
A cricket tournament scheduler wants every team to play every other team at every ground. Pairing each item of one list with each item of another is a Cartesian product, and that is exactly what CROSS JOIN does. There is no ON condition.
Example
CREATE TABLE sizes (size TEXT); -- S, M, L
CREATE TABLE colours (colour TEXT); -- Red, Blue
SELECT size, colour
FROM sizes
CROSS JOIN colours;With 3 sizes and 2 colours the result has 3 x 2 = 6 rows: S-Red, S-Blue, M-Red, M-Blue, L-Red, L-Blue.
The old comma style does the same thing:
SELECT size, colour FROM sizes, colours;Real uses are product variants, calendar grids (dates x slots) and test data. The danger is an accidental cross join: forgetting the ON or WHERE in a normal join between two 100,000-row tables produces 10 billion rows.
What to say in the interview
"CROSS JOIN returns the Cartesian product, so the row count is the product of both tables. I use it deliberately to build combinations. An unexpectedly huge result usually means a missing join condition."
Common mistakes
- Writing
FROM a, band forgetting the WHERE that links them.