CROSS JOIN | Engineering Interview Kit

Concept

A CROSS JOIN produces a Cartesian Product. It multiplies every single row in Table A by every single row in Table B.
Unlike all other joins, a CROSS JOIN does not use an ON clause. It does not care about relationships or Primary Keys. It just blindly combines everything.

Mental Model

Colors Table (3 rows): Red, Blue, Green
Sizes Table (3 rows): Small, Medium, Large

If you CROSS JOIN them, the database will output 9 rows ( $3 \times 3$ ).
Red-Small, Red-Medium, Red-Large, Blue-Small, Blue-Medium, Blue-Large, Green-Small, Green-Medium, Green-Large.

Syntax & Examples

-- Generates all possible combinations of colors and sizes
SELECT colors.name, sizes.name
FROM colors
CROSS JOIN sizes;

Real-World Usage

In standard application logic, CROSS JOINs are almost always a mistake (an accidental Cartesian explosion caused by forgetting an ON clause). However, they have highly specific niche uses in Data Engineering and Analytics.

1. Generating Missing Data for Reports (Zero-Filling)

Imagine you want a report showing sales for every day in January. But on Jan 5th, your store had 0 sales. Your normal GROUP BY query completely skips Jan 5th, leaving a gap in your line chart.
To fix this, you create a temporary table containing all 31 days of January. You CROSS JOIN it with your list of store locations to create a perfect matrix of every store on every day. Then, you LEFT JOIN your actual sales data onto that matrix, ensuring that Jan 5th appears with a 0.

2. Creating Massive Test Data

If you need 10 million fake rows to load-test an index, you don’t need to insert them one by one. You can CROSS JOIN a table of 1,000 first names with a table of 10,000 last names to instantly generate a temporary table of 10 million unique people in memory.

Interview Questions

Q: A junior developer writes: SELECT * FROM users, orders;. What kind of join is this, and why is it bringing down the production database?
A: This is the old, implicit syntax for a CROSS JOIN. Because there is no WHERE clause specifying how the tables relate, the database generates a full Cartesian Product. If users has 10,000 rows and orders has 100,000 rows, the database attempts to generate 1 Billion rows in RAM, exhausting all server memory and causing an Out-Of-Memory (OOM) crash.

Q: Can you achieve the exact same result as a CROSS JOIN using an INNER JOIN?
A: Yes. If you write an INNER JOIN but provide a tautology (a condition that is always mathematically true) in the ON clause, it forces a Cartesian Product.
For example: SELECT * FROM A INNER JOIN B ON 1 = 1;
This behaves identically to a CROSS JOIN. However, explicitly writing CROSS JOIN is better practice as it signals your intent to other developers that the Cartesian product is intentional, not a bug.