What is a CROSS JOIN, and when would you use one?
Quick Answer
A `CROSS JOIN` produces the Cartesian product of two tables — every row of the first paired with every row of the second, with no join condition. It's rarely used to combine unrelated business data (row counts multiply, N×M), but is genuinely useful for generating combinations: date × store, size × color, or a numbers/calendar table joined against real data to fill gaps.
Detailed Answer
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
If sizes has 4 rows and colors has 6 rows, this returns 24 rows — every combination, with no matching condition at all.
Legitimate use cases
Generating a full combination matrix — e.g., every product variant (size × color) that could exist, even before any have been created:
SELECT p.product_id, s.size, c.color
FROM products p
CROSS JOIN sizes s
CROSS JOIN colors c;
Filling gaps with a calendar/numbers table — a very common reporting pattern. Suppose you want a row for every day in a range, even days with zero sales:
SELECT d.day, COALESCE(SUM(s.amount), 0) AS total_sales
FROM generate_series('2024-01-01'::date, '2024-01-31'::date, '1 day') AS d(day)
CROSS JOIN stores st
LEFT JOIN sales s ON s.sale_date = d.day AND s.store_id = st.id
GROUP BY d.day, st.id;
This cross-joins a generated date series against every store, then left-joins actual sales — guaranteeing every (day, store) pair appears in the output, with zero-sales days showing 0 instead of being silently missing.
The accidental CROSS JOIN
The more important interview point: an accidental Cartesian product from a missing or wrong join condition is one of the most common real-world SQL bugs:
-- BUG: no ON clause and old-style comma syntax with no WHERE — this is
-- effectively a CROSS JOIN, silently multiplying every order by every customer.
SELECT o.id, c.name
FROM orders o, customers c;
If orders has 10,000 rows and customers has 5,000, that's 50 million rows — usually surfacing as a query that "hangs" or a report with wildly inflated totals, not an obvious error. Always double check that every table in a join has an explicit, correct join condition.