What does a CROSS JOIN do, and when is it actually useful?

How to think about it

CROSS JOIN is the one join with no ON clause, by design — it pairs every left row with every right row. Most people can recite “Cartesian product” but stall on a real use case, which is exactly what the interviewer is probing. Lead with the definition, then immediately show it earning its keep.

SELECT a.val, b.val
FROM table_a a
CROSS JOIN table_b b;       -- M × N rows; the comma-FROM form does the same but reads worse

Where it’s actually useful

A date spine. Analytics often needs a row for every day in a range, even days with no events, so a chart shows zeros instead of gaps. Cross-join users against a calendar, then LEFT JOIN the events:

SELECT u.user_id, d.date_day
FROM users u
CROSS JOIN all_dates d
WHERE d.date_day BETWEEN '2024-01-01' AND '2024-03-31';

A dimension grid. Every (product, store) pair for inventory planning — start with all combinations, then LEFT JOIN inventory to find the gaps:

SELECT p.product_id, s.store_id
FROM products p
CROSS JOIN stores s;

A worked example — a parameter grid

Applying a set of parameters to every row is the cleanest demonstration — here, three discount rates against three products:

WITH discounts(rate) AS (VALUES (0.05), (0.10), (0.20))
SELECT p.name, p.price, d.rate,
       ROUND(p.price * (1.0 - d.rate), 2) AS discounted_price
FROM products p
CROSS JOIN discounts d
ORDER BY p.name, d.rate;

name	price	rate	discounted_price
Widget A	100.0	0.05	95.0
Widget A	100.0	0.1	90.0
Widget A	100.0	0.2	80.0
Widget B	250.0	0.05	237.5
Widget B	250.0	0.1	225.0
Widget B	250.0	0.2	200.0
Widget C	80.0	0.05	76.0
Widget C	80.0	0.1	72.0
Widget C	80.0	0.2	64.0

Three products times three rates gives nine rows — each product priced at every discount, all from one statement. That fan-out is the feature here: you deliberately want every combination, which is precisely when CROSS JOIN is the right tool rather than an accident.

What to say in the room

Lead with “It’s the Cartesian product — every left row paired with every right row,” then immediately give the date-spine or dimension-grid use case. That jump from definition to application is what separates people who’ve read about CROSS JOIN from people who’ve used it.