CROSS JOIN
Learning Focus
Use this lesson to understand CROSS JOIN — generating the Cartesian product of two tables.
Concept Overview
A CROSS JOIN returns every possible combination of rows from two tables. If table A has 3 rows and table B has 4 rows, the result has 12 rows.
Table A: [r1, r2, r3] × Table B: [c1, c2, c3, c4]
= 12 combinations
Examples
Example 1: Generate All Size-Color Combinations
CREATE TABLE sizes (size VARCHAR);
CREATE TABLE colors (color VARCHAR);
INSERT INTO sizes VALUES ('S'), ('M'), ('L'), ('XL');
INSERT INTO colors VALUES ('Red'), ('Blue'), ('Green');
-- All 12 combinations
SELECT size, color FROM sizes CROSS JOIN colors ORDER BY size, color;
Example 2: Date x Category Matrix
-- Generate a report scaffold for every month x department
SELECT d.month_label, dept.department
FROM (SELECT '2024-01' AS month_label UNION ALL SELECT '2024-02' UNION ALL SELECT '2024-03') d
CROSS JOIN (SELECT DISTINCT department FROM employees) dept
ORDER BY month_label, department;
Example 3: CROSS JOIN with range() (DuckDB-specific)
-- Generate combinations using range()
SELECT a.n AS n1, b.n AS n2
FROM (SELECT unnest(range(1,4)) AS n) a
CROSS JOIN (SELECT unnest(range(1,4)) AS n) b
ORDER BY n1, n2;
When to Use CROSS JOIN
| Use Case | Example |
|---|---|
| Combinatorial grids | Size × Color product variants |
| Report scaffolding | All months × all categories |
| Test data generation | Random pairings |
| Distance matrices | Every city to every city |
Common Pitfalls
| Pitfall | Consequence | Prevention |
|---|---|---|
| Accidental CROSS JOIN | Row explosion — billions of rows | Always verify join type; use EXPLAIN |
| No WHERE to filter the product | Unusably large result | Add filtering conditions after cross join |
| Confusing CROSS JOIN with INNER JOIN | Wrong business logic | CROSS JOIN = no join condition by design |
Quick Reference
SELECT a.col, b.col FROM a CROSS JOIN b;
SELECT * FROM t1, t2; -- implicit CROSS JOIN (avoid this style)
What's Next
- Next: UNION, INTERSECT, EXCEPT - Combine result sets vertically.
- Module Overview - Return to this module index.