CROSS JOIN
Overview
The CROSS JOIN is the mathematical 'Cartesian Product' of two tables. Unlike every other join, a CROSS JOIN has absolutely no ON condition. It does not attempt to match data. Instead, it takes every single row in Table A, and multiplies it by every single row in Table B. If you have 10 Colors and 10 Sizes, a CROSS JOIN instantly generates all 100 possible combinations. It is a highly specialized, dangerous tool.
Syntax
-- Syntax: CROSS JOIN (Notice there is no ON clause!)
SELECT
sizes.size_name,
colors.color_name
FROM sizes
CROSS JOIN colors;
-- Legacy Syntax (Comma separated in the FROM clause)
-- STRONGLY DISCOURAGED in modern SQL, but you will see it in old codebases.
SELECT sizes.size_name, colors.color_name
FROM sizes, colors;Common Pitfalls
- Accidentally triggering a Cartesian Explosion. If you have a
userstable with 1 million rows, and anorderstable with 1 million rows, and you accidentally write aCROSS JOIN(or forget theONclause in a regular join), the database will attempt to generate 1 Trillion rows in memory (1M x 1M). This will instantly crash the database server. - Using
CROSS JOINwhen you actually meant to filter data. ACROSS JOINis purely for generating exhaustive combinations, typically for inserting missing data or building matrix reports.
Interview Questions
CROSS JOIN to fill in missing gaps in a time-series sales report?If a store had zero sales on Tuesday, a standard GROUP BY date will completely omit Tuesday from the chart. You can CROSS JOIN a table of 'All Possible Dates' with a table of 'All Stores' to generate a perfect matrix, then LEFT JOIN the actual sales data onto that matrix, ensuring Tuesday shows up as $0 instead of vanishing.
Real-World Example
Generating every possible combination of a product for an inventory system.
INSERT INTO product_variants (shirt_id, size, color)
SELECT
99 AS shirt_id,
s.size_name,
c.color_name
FROM config_sizes s
CROSS JOIN config_colors c;Check Your Knowledge
Test your understanding of CROSS JOIN with these quick questions.