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

How to think about it

CROSS JOIN is the one join that has no ON clause by design. Most engineers know the definition but struggle to name a real use case — which is exactly what the interviewer is probing. Lead with “Cartesian product,” then immediately demonstrate practical utility.

Syntax

SELECT a.val, b.val
FROM table_a a
CROSS JOIN table_b b;

An implicit form also exists (comma-separated FROM clause), but explicit CROSS JOIN is far more readable and makes intent unambiguous.

Use case 1: generating a complete date spine

Analytics often needs a row for every date in a range, even dates with no events. Without it, your time-series chart has missing days instead of zeros.

-- all_dates: one row per calendar day
-- users: one row per user
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';

Then LEFT JOIN your events table on (user_id, date_day) — rows with no events get NULLs, which you COALESCE to 0.

Use case 2: all product-store combinations

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

Useful for inventory planning: start with every possible (product, store) pair, then LEFT JOIN inventory to find which combinations are missing stock records.

Use case 3: applying a set of parameters to every row

What to say in an interview

Lead with “It’s the Cartesian product — every row from the left paired with every row from the right.” Then give the date-spine or dimension-grid use case immediately. That’s what separates candidates who can define it from candidates who have actually used it.