Understanding CROSS JOIN Syntax and Mechanics
You'll learn what a CROSS JOIN does, how it creates every possible combination of rows, and when you'd actually want to use it.
What is a CROSS JOIN?
A CROSS JOIN produces a Cartesian product of two tables—every row from the first table is paired with every row from the second table. Unlike the joins you've learned (INNER, LEFT, RIGHT, FULL OUTER), a CROSS JOIN has no join condition with ON or WHERE. It simply combines everything with everything.
Think of it like this: if you have 3 shirts and 4 pairs of pants, a CROSS JOIN gives you all 12 possible outfits (3 × 4 = 12).
Basic Syntax
SELECT columns
FROM table1
CROSS JOIN table2;
Or the older implicit syntax (without the keyword):
SELECT columns
FROM table1, table2;
How It Works
If colors has 5 rows and sizes has 3 rows, the result will have 15 rows (5 × 3). Each color appears with each size exactly once.
When to Use CROSS JOIN
CROSS JOINs are appropriate when you genuinely need all combinations:
- Generating test data or scenarios
- Creating calendar tables (dates × hours)
- Building product catalogs (all size/color combinations)
- Mathematical operations requiring pairwise comparisons
Warning
CROSS JOINs grow exponentially large. Two tables with 1,000 rows each produce 1,000,000 result rows! Use them intentionally, never accidentally by forgetting a join condition.
Key Takeaway: A CROSS JOIN creates every possible row combination between two tables (Cartesian product) without any filtering condition—use it only when you genuinely need all pairings.