SQL CROSS JOIN — Generate All Row Combinations

What Is a CROSS JOIN?

A CROSS JOIN produces the Cartesian product of two tables — every row from the left table is paired with every row from the right table. If Table A has m rows and Table B has n rows, the result contains m * n rows. No join condition (ON clause) is used.

Syntax

-- Explicit CROSS JOIN
SELECT s.size_name, c.color_name
FROM sizes AS s
CROSS JOIN colors AS c;

-- Implicit (comma) syntax — equivalent but less readable
SELECT s.size_name, c.color_name
FROM sizes s, colors c;

Venn Diagram Interpretation

A Venn diagram is not a perfect metaphor for CROSS JOIN because there is no matching condition. Both circles are fully highlighted to indicate that all data from both tables participates, but the overlapping region does not have special meaning.

Result Size Warning

Cross joins grow multiplicatively. A table with 1,000 rows crossed with another 1,000-row table produces 1,000,000 rows. Always verify the table sizes before running a cross join in production:

SELECT
  (SELECT COUNT(*) FROM sizes) *
  (SELECT COUNT(*) FROM colors) AS expected_rows;

Practical Uses

1. Combination generation: Create every size/color combination for a product catalog.
2. Calendar grids: Cross months with days-of-week to build a scheduling matrix.
3. Test data: Generate a large result set from two small seed tables.
4. Reporting: Pair every sales region with every product for a pivot table skeleton.

Accidental Cross Joins

Forgetting the ON clause in an INNER JOIN or using the comma syntax without a WHERE clause produces an unintentional cross join. This is one of the most common SQL mistakes and can bring a database to its knees if the tables are large.

-- BUG: missing ON clause produces a cross join
SELECT * FROM orders JOIN customers;

Always include an explicit ON condition for non-CROSS joins.