CROSS JOIN in SQL: What It Is and When You Actually Want One

CROSS JOIN pairs every row of one table with every row of another — no join condition, no filtering, just the full Cartesian product. It has a reputation as a mistake (and the accidental kind really is a query killer), but the deliberate kind is the cleanest way to build date scaffolds, generate combinations, and broadcast a single computed value across a whole result set.

What does CROSS JOIN return?

Every possible pairing. Three sizes crossed with four colors yields twelve rows — each size repeated once per color. There is no ON clause because nothing is being matched; the output is pure combination, which is precisely why row counts multiply instead of merge.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

-- 3 sizes × 4 colors = 12 rows:
-- S red, S blue, S green, S black,
-- M red, M blue, ... and so on

-- Equivalent old-style syntax (avoid — too easy to misread):
SELECT s.size, c.color FROM sizes s, colors c;

Prefer the explicit CROSS JOIN keyword over the comma form: it tells every future reader the Cartesian product is intentional, not a forgotten WHERE clause.

When is a CROSS JOIN actually useful?

Whenever you need "every combination of X and Y" as a skeleton to hang data on. The most common production use is the date scaffold: reports must show a row for every product on every day, including days with zero sales — something an INNER JOIN on the sales table can never give you, because it only returns combinations that exist.

-- Scaffold: every product × every day, then attach actual sales
SELECT
    d.calendar_date,
    p.product_id,
    COALESCE(SUM(s.amount), 0) AS revenue
FROM calendar_days d
CROSS JOIN products p
LEFT JOIN sales s
    ON  s.product_id = p.product_id
    AND s.sale_date  = d.calendar_date
WHERE d.calendar_date >= '2026-07-01'
GROUP BY d.calendar_date, p.product_id
ORDER BY d.calendar_date, p.product_id;

Other legitimate uses: generating test data, producing all variant combinations (size × color × material) for a product catalog, pairing every row with a one-row subquery of grand totals so you can compute percentages, and building parameter grids for simulations.

-- Broadcast a single total across all rows (1-row cross join is free)
SELECT
    p.product_id,
    p.revenue,
    ROUND(100.0 * p.revenue / t.total_revenue, 2) AS pct_of_total
FROM product_revenue p
CROSS JOIN (SELECT SUM(revenue) AS total_revenue
            FROM product_revenue) t;

How do I spot an accidental cross join?

The symptoms are unmistakable: a query that suddenly takes minutes instead of seconds, aggregates that are mysteriously inflated by a suspiciously round factor, or a result set with far more rows than the largest table involved. The causes are almost always one of three: a missing ON clause with comma-style joins, a join condition that references the wrong table, or an ON clause where both sides come from the same table.

-- BUG: comma join with a WHERE that filters but never joins
SELECT o.order_id, c.name
FROM orders o, customers c        -- Cartesian product!
WHERE o.amount > 100;             -- filters rows, joins nothing

-- BUG: ON clause compares a table to itself
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = o.customer_id;  -- always true!

-- FIXED
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Modern engines flag some of these — and an execution plan showing a "Nested Loop" with no join predicate over two large inputs is the classic tell. Checking plans is a habit worth building; our guide to optimizing joins covers what else to look for.

How big does a cross join get?

Do the multiplication before you run it. Output rows equal the product of the input row counts, and the growth is brutal: two 1,000-row tables produce a million rows; two 100,000-row tables produce ten billion. A cross join between a modest fact table and a modest dimension can exceed the storage of your entire warehouse.

Table A rows	Table B rows	Cross join output
10	10	100
1,000	1,000	1,000,000
100,000	100,000	10,000,000,000
1,000,000	1,000,000	1,000,000,000,000

The safe pattern for intentional cross joins is small × small, or small × filtered: cross a 365-row date spine with a few hundred products, never raw fact tables. Filter both inputs before the cross join — with a WHERE inside a subquery or CTE — so the multiplication happens on the smallest possible sets.

-- Size the inputs first, then cross
WITH days AS (
    SELECT calendar_date FROM calendar_days
    WHERE calendar_date BETWEEN '2026-08-01' AND '2026-08-31'  -- 31 rows
),
active AS (
    SELECT product_id FROM products WHERE is_active = TRUE      -- 200 rows
)
SELECT d.calendar_date, a.product_id
FROM days d CROSS JOIN active a;   -- 6,200 rows: perfectly fine