CROSS JOIN
Generate every possible combination of rows from two tables
Intermediate 8 minCROSS JOINcartesian
Cartesian Product
CROSS JOIN produces every possible combination of rows from two tables. No ON condition — it generates the pure Cartesian product. Here's what to know:
Multiplicative output — 3 rows × 4 rows = 12 result rows. Two 1000-row tables produce 1,000,000 rows. Always use CROSS JOIN deliberately and only with small tables to avoid accidentally generating massive result sets.
Practical uses — Generating all date-category combinations for a report scaffold, creating test data from template values, building comparison matrices, and pairing every item with every other item for analysis.
No ON clause — Unlike INNER and LEFT JOIN, CROSS JOIN has no matching condition at all. Every single left row pairs with every single right row. The result size is always the product of both table sizes.
Knowledge Canvas
How CROSS JOIN Works
Generate every combination
- Produces cartesian product: every left row × every right row
- No ON clause needed — all combinations are included
- 10 rows × 10 rows = 100 rows in the result
- Rarely used by accident — but devastating when it happens
Cross Join Dangers
Row explosion warning
- 1,000 × 1,000 = 1,000,000 rows — grows quadratically
- Missing ON clause in INNER JOIN = accidental cross join
- Most "cross join" results are bugs, not features
- Always verify row counts after joins
Legitimate Uses
When cross join is actually correct
- Generate all date × product combinations (fill gaps)
- Create lookup grids: sizes × colors
- Test data generation
- Scaffold: all users × all permission types
Safety Rules
Keep cross joins under control
- Only cross join small reference tables
- Always add WHERE or LIMIT as a safety net
- Document intent: comment why cross join is deliberate
- Verify expected row count before running
Syntax
Syntax Template
1SELECT
2 a.col,
3 b.col
4FROM table_a a
5CROSS JOIN table_b b;
CROSS JOINNo ON clause — all combinationsWarningOutput = rows_A × rows_B — use with small tables Sample Data
products
10 rows
| id | name | category |
|---|---|---|
| P101 | Wireless Mouse | Electronics |
| P102 | Office Desk | Furniture |
| P103 | LED Desk Lamp | Furniture |
| P104 | USB-C Cable | Electronics |
| P105 | Bluetooth Speaker | Electronics |
| P106 | Office Chair | Furniture |
| P107 | Coffee Maker | Appliances |
| P108 | Notebook Set | Stationery |
| P109 | Wireless Headphones | Electronics |
| P110 | Standing Desk | Furniture |
customers
12 rows
| id | name | city | country | |
|---|---|---|---|---|
| C1001 | Aarav Sharma | [email protected] | Mumbai | India |
| C1002 | Sara Chen | [email protected] | Singapore | Singapore |
| C1003 | James Wilson | [email protected] | London | UK |
| C1004 | Maria Garcia | [email protected] | Madrid | Spain |
| C1005 | Yuki Tanaka | [email protected] | Tokyo | Japan |
| C1006 | Priya Patel | [email protected] | Delhi | India |
| C1007 | Alex Johnson | [email protected] | New York | USA |
| C1008 | Chen Wei | [email protected] | Shanghai | China |
| C1009 | Emma Brown | [email protected] | Sydney | Australia |
| C1010 | Omar Hassan | [email protected] | Dubai | UAE |
| C1011 | Lena Muller | [email protected] | Berlin | Germany |
| C1012 | Ravi Kumar | [email protected] | Bangalore | India |
Worked Example
Generate every unique pair of product categories.
SQL
1SELECT DISTINCT
2 a.category AS cat_1,
3 b.category AS cat_2
4FROM products a
5CROSS JOIN products b
6WHERE a.category < b.category
7ORDER BY
8 cat_1,
9 cat_2;
CROSS JOIN pairs every product with every other product. DISTINCT collapses duplicate category pairs. The WHERE a.category < b.category keeps only unique unordered pairs.
Output
3 rows
| cat_1 | cat_2 |
|---|---|
| Appliances | Electronics |
| Appliances | Furniture |
| Appliances | Stationery |
Key Concepts
1Output = rows_A × rows_B — grows multiplicatively
2No ON clause — every left row pairs with every right row
3Use only with small tables or for deliberate scaffolding
41000 × 1000 = 1,000,000 rows — be careful with table sizes
Pro Tip
Rarely used in basic queries, but essential for generating complete grids and scaffolds — especially reports that need a row for every combination even when data is missing.
When to Use
Report scaffolds (every month × every product), test data generation, date dimensions, all-pairs comparisons.
How many total row combinations would a CROSS JOIN between customers and products produce? Use COUNT(*).
Your Query