Lesson Beginner

CROSS JOIN

CROSS JOIN returns every combination of rows from two tables: the Cartesian product. No ON clause, no matching key. If table A has n rows and table B has m rows, you get n × m rows out.
You use it intentionally, for scenario grids, date spines, and template expansion. You almost never want it by accident.

Syntax #

sql

SELECT
  a.col,
  b.col
FROM table_a AS a
CROSS JOIN table_b AS b;

Notice there's no ON. There's no join condition to write, because every pair is valid.

The Scenario Grid #

The most common honest use of CROSS JOIN is to build a dense grid: every (month, channel), every (region, product), every (user, date). You then LEFT JOIN a fact table onto the grid and COALESCE missing metrics to zero. This guarantees that missing combinations show up as zeros instead of simply being absent from the output.

(The example below uses WITH to name small inline tables. WITH names a subquery, covered properly in the CTE lesson later. For now, read it as a named intermediate result.)

sql

-- Build every (month, channel) slot, then attach actual revenue
WITH months AS (
  SELECT * FROM (VALUES ('2025-01'), ('2025-02'), ('2025-03')) AS t(month_id)
),
channels AS (
  SELECT * FROM (VALUES ('paid_search'), ('organic'), ('email')) AS t(channel)
)
SELECT
  m.month_id,
  c.channel
FROM months AS m
CROSS JOIN channels AS c
ORDER BY m.month_id, c.channel;

Warning

Row counts multiply fast. 1,000 × 1,000 is a million rows. 100,000 × 100,000 is ten billion, enough to lock up a database. Always estimate n × m × ... before running a CROSS JOIN against real tables. If the product exceeds what you'd willingly paginate through, you've written the wrong join.

CROSS JOIN vs Missing ON #

Writing FROM a, b with no WHERE join condition also produces a Cartesian product, implicitly. It's the classic "forgot the join condition" bug, and it's identical to CROSS JOIN except it looks like a mistake. Always write CROSS JOIN explicitly when you mean it, so reviewers don't have to guess whether it was intentional.

Try It #

Build a (category × month) reporting grid from the real products and orders tables: every category paired with every month, whether that category sold anything or not.

Loading SQL editor...

Practice #

Build a status × product_id grid for reporting slots. Use the seeded products table and a pre-built statuses CTE. Return columns status and product_id, one row per combination. No ordering requirement.

Loading SQL editor...

Mistakes to Watch For #

Accidental cross joins from forgetting the ON condition. Always write CROSS JOIN when you mean it and use explicit ON otherwise.
Running CROSS JOIN on large real tables and blowing out memory; estimate n × m first.
Using CROSS JOIN to "just try all combinations" when a GROUP BY or DISTINCT would answer the question more cheaply.
Forgetting to LEFT JOIN the fact table back onto the grid; a scenario grid is useless without the metrics it slots into.

Knowledge check #

3 questions

0 / 3 answered

CROSS JOIN of a 1,000-row table with another 1,000-row table returns:
A legitimate use of CROSS JOIN is:
Old-style implicit cross join: SELECT * FROM A, B. What goes wrong if you forget the WHERE?

Next Step #

Continue to Self join for joining a table to itself: hierarchies, pair comparisons, and self-relationships.