SQL CROSS JOIN: Cartesian Products, Generating Combinations and Calendars

CROSS JOIN is the one join that matches no rows by any condition. It takes every row from the left table and pairs it with every row from the right. The result is a Cartesian product: 4 rows on the left and 7 on the right give you exactly 28 rows out. It sounds exotic, but in practice it's a workhorse — generating combinations, filling gaps in reports, and building gap-free calendars. It's also the single most common cause of suddenly bloated queries, when a join sneaks in where nobody asked for one.

What a Cartesian product is

The syntax is as simple as it gets — there's no ON, because there is no join condition:

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

If sizes holds S, M, L and colors holds red, blue, you get all 6 pairs: S/red, S/blue, M/red, and so on. The output row count is simply the product of the input row counts.

There's also an "implicit" comma form that does exactly the same thing:

-- equivalent to CROSS JOIN
SELECT s.size, c.color
FROM sizes AS s, colors AS c;

That form is dangerous: a comma is easy to type by accident, and then you get a cross join instead of the join you meant. So the rule of thumb is to always write CROSS JOIN explicitly when you genuinely want a Cartesian product.

• CROSS JOIN has no ON and filters nothing.
• Row count = count(left) * count(right).
• The syntax is identical in PostgreSQL, MySQL, and ClickHouse. ClickHouse, however, rewrites CROSS JOIN into a conditionless INNER JOIN and materializes the right-hand table in memory — on large data that can eat your RAM fast.

Generating combinations

The most common practical use is building a "full matrix" of options. Say you want an inventory row for every product-warehouse pair, even where there's no stock yet:

INSERT INTO inventory (product_id, warehouse_id, qty)
SELECT p.id, w.id, 0
FROM products  AS p
CROSS JOIN warehouses AS w;

A related trick is computing a metric matrix for a report where every combination must appear, not just the ones present in the data. Here CROSS JOIN builds the skeleton and a LEFT JOIN pulls in the facts:

SELECT r.name AS region,
       c.name AS category,
       COALESCE(SUM(o.amount), 0) AS revenue
FROM regions     AS r
CROSS JOIN categories AS c
LEFT JOIN orders AS o
       ON o.region_id   = r.id
      AND o.category_id = c.id
GROUP BY r.name, c.name
ORDER BY r.name, c.name;

Without the CROSS JOIN, regions with no sales in a given category would simply vanish from the report. With it, a revenue = 0 row shows up explicitly — and that's usually exactly what the business wants.

Calendars and filling gaps

A classic task: build a per-day report where every date is present, even on days with no orders. In PostgreSQL, generate_series produces the date series:

-- gap-free calendar for June, with orders per day
SELECT d::date AS day,
       COUNT(o.id) AS orders_count
FROM generate_series(DATE '2026-06-01',
                     DATE '2026-06-30',
                     INTERVAL '1 day') AS d
LEFT JOIN orders AS o
       ON o.created_at::date = d::date
GROUP BY d
ORDER BY d;

Where CROSS JOIN really shines is when you need a calendar per user or per product. Cross-join the list of days with the list of entities and you get a dense "day × user" grid:

SELECT u.id AS user_id,
       d::date AS day,
       COUNT(o.id) AS orders
FROM users AS u
CROSS JOIN generate_series(DATE '2026-06-01',
                          DATE '2026-06-07',
                          INTERVAL '1 day') AS d
LEFT JOIN orders AS o
       ON o.user_id = u.id
      AND o.created_at::date = d::date
GROUP BY u.id, d
ORDER BY u.id, day;

Cross-engine differences:

• MySQL has no generate_series. You build a date series with a recursive CTE (WITH RECURSIVE) or a "numbers/tally" table.
• ClickHouse offers numbers(N) plus arrayJoin to expand a range into rows.

Accidental cross joins and how to avoid them

A cross join is treacherous because it doesn't error out — it silently returns far too many rows. The usual cause is a comma plus a forgotten condition in WHERE:

-- BUG: no join predicate -> Cartesian product
SELECT u.name, o.amount
FROM users u, orders o;          -- forgot WHERE u.id = o.user_id

The symptom is an unexpectedly huge result and inflated aggregates: every sum gets multiplied by the number of rows in the other table. If a query suddenly reports revenue ten times too high, the first thing to check is whether rows got multiplied by a stray join.

How to protect yourself:

• Always write explicit JOIN ... ON instead of comma joins.
• If you genuinely need a Cartesian product, write CROSS JOIN explicitly. It tells the reviewer the explosion is intentional.
• Check COUNT(*) before and after adding a join: if the row count jumped by a clean multiple, it's almost certainly an accidental cross join.
• Read EXPLAIN: a Nested Loop with no join condition over two large tables is a red flag.

Gotcha: CROSS JOIN is expensive. The product grows multiplicatively, so 100k × 100k rows is already 10 billion. Only cross-join small tables (days, categories, sizes) and bring in large facts via a LEFT JOIN to that skeleton.

CROSS JOIN is a simple tool with clear semantics: everything against everything. Reach for it deliberately when generating combinations and calendars — and keep that comma under suspicion so it never shows up uninvited.