skip to content

CROSS JOIN

16 roadmaps5 questions

The Cartesian product: every row paired with every row. You need to know both its deliberate uses (generating combinations, date spines) and how a forgotten ON clause creates one by accident and explodes your row count.

part ofSQLoverview, primer and where to startread it →
on this pageshow

explore

questions

5

How many rows does a CROSS JOIN of a 100-row table and a 20-row table return, and why?

level: juniorimportance: must knowfreq 78%

answer

  1. no matching condition is involved
  2. every left row meets every right row
  3. the count is a product, not a sum
  4. multiply the two row counts together

basics

~20 s

2,000 rows. A CROSS JOIN returns the Cartesian product: every row of the left table paired with every row of the right, with no join predicate. The output size is N times M, never N plus M.

solid answer

~40 s

A `CROSS JOIN` is the only join with no `ON` condition — it pairs each left row with each right row unconditionally, so 100 rows crossed with 20 rows gives 100 × 20 = 2,000 rows. Because nothing filters the pairing, no row is ever dropped for lack of a match and no column is ever NULL-extended, which is what separates it from inner and outer joins. The result is a product, so it grows multiplicatively: cross three tables of 1,000 rows each and you have a billion rows. Two edge cases follow straight from the arithmetic: if either side is empty the result is empty (N × 0 = 0), and crossing with a one-row table just copies that row's columns onto every row of the other side.

code

sql · 5 lines
sql
-- 3 colors x 2 sizes = 6 rows
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s
ORDER BY c.color, s.size;

go deeper

for a junior

Recall the formula N times M and be able to enumerate a small example out loud, such as three colors crossed with two sizes giving six rows. Know that CROSS JOIN takes no ON clause.

for a middle

Explain why the count multiplies rather than adds, what happens when one side is empty or has exactly one row, and that a comma-separated FROM list with no relating predicate is the same operation written implicitly.

for a senior

Be ready to use the arithmetic diagnostically: when a query returns a suspicious row count, check whether it factors into the sizes of two joined tables, and say what predicate is missing.

for a principal

Frame it as a review habit — insist that any intentional Cartesian product is written with the explicit CROSS JOIN keyword so reviewers can distinguish intent from a forgotten join predicate.

## What CROSS JOIN does `CROSS JOIN` is the unconditional join. It takes two row sources and emits one output row for every possible pairing of a row from the left with a row from the right. Unlike `INNER JOIN`, `LEFT JOIN` and the rest, it takes no `ON` clause and no `USING` list, because there is nothing to decide: every pair qualifies. ```sql SELECT c.color, s.size FROM colors c CROSS JOIN sizes s; ``` If `colors` holds `red, green, blue` and `sizes` holds `S, M`, the result is six rows: `(red,S) (red,M) (green,S) (green,M) (blue,S) (blue,M)`. ## The arithmetic The rule is simply `rows(left) × rows(right)`. For a 100-row table crossed with a 20-row table that is 2,000 rows. The mistake interviewers listen for is addition — answering 120 — which betrays a mental model of "stacking" rather than "pairing". Stacking is what `UNION ALL` does; multiplication is what a join does. The multiplication chains. `a CROSS JOIN b CROSS JOIN c` returns `|a| × |b| × |c|`. Three tables of a thousand rows each produce a billion output rows — which is why an accidental cross join is one of the classic ways to make a small database look like a hung server. The operation is commutative in content: `a CROSS JOIN b` and `b CROSS JOIN a` contain the same set of pairings and the same row count, differing only in column order (and in whatever order the engine happens to return rows, which is undefined without `ORDER BY`). ## Edge cases that fall out of the formula - **Empty side.** If either input has zero rows, the product is zero rows. There is no NULL-extension: `CROSS JOIN` never invents a partner the way an outer join does. - **One-row side.** Crossing an N-row table with a one-row table returns exactly N rows, with the single row's columns copied onto each. This is a deliberate and common idiom for attaching a grand total or a parameter row to every result row. - **Duplicates.** `CROSS JOIN` does not deduplicate. If the left table has three identical rows, each of them is paired with every right row, so all three pairings appear. ## Syntax forms The standard spells it `A CROSS JOIN B`. Writing the two tables in a comma-separated `FROM` list — `FROM colors, sizes` — with no predicate relating them yields exactly the same Cartesian product; the explicit keyword is preferred precisely because it announces the intent, so a reviewer can tell a deliberate product from a forgotten predicate. Some engines also accept `A JOIN B ON TRUE` or `A INNER JOIN B ON 1 = 1`, which is the same thing written as an always-true predicate. ```sql -- these three describe the same result SELECT * FROM colors CROSS JOIN sizes; SELECT * FROM colors, sizes; SELECT * FROM colors JOIN sizes ON TRUE; ``` ## Why anyone wants a Cartesian product Deliberate cross joins solve the problem of rows that *should* exist but do not. A sales table contains no row for a product that sold nothing on Tuesday, so a report built only from sales silently omits Tuesday. Crossing a list of days with a list of products manufactures the full grid of `(day, product)` combinations first, and the real data is attached to that grid afterwards. The same shape generates test fixtures (every plan crossed with every currency), pairing matrices, and per-row copies of a single summary row. ## Reasoning about the number in an interview When a query returns far more rows than the base table has, the first arithmetic to run is exactly this one: is the observed count a plausible product of two table sizes? A join returning 20,000,000 rows out of tables of 10,000 and 2,000 rows is not a mystery — it is 10,000 × 2,000, and the missing piece is a join predicate. Being fluent with N × M is what makes that diagnosis instant rather than a debugging session.

  • What does a CROSS JOIN return when one of the two tables is empty?
    Zero rows. The result size is the product of the two row counts, and anything times zero is zero. A `CROSS JOIN` never NULL-extends the surviving side the way an outer join does — there is no notion of an unmatched row, so an empty input simply annihilates the result.
  • Does the order of the two tables change the result of a CROSS JOIN?
    Not in content or cardinality: `a CROSS JOIN b` and `b CROSS JOIN a` produce the same set of pairings and the same row count. Only the column order in `SELECT *` differs. Row order is undefined for either form unless you add `ORDER BY`.
  • How does CROSS JOIN differ from UNION ALL, which also makes a result bigger?
    `UNION ALL` stacks rows vertically: it concatenates two result sets that must have compatible column counts and types, giving N + M rows and the same columns. `CROSS JOIN` widens horizontally: it pairs rows, giving N × M rows with the columns of both tables side by side.

It is a restaurant menu's build-your-own section: three breads and four fillings do not give you seven choices, they give you twelve sandwiches — every bread with every filling.

saying these in an interview costs you the question

  • Answers 120, adding the row counts instead of multiplying
  • Thinks CROSS JOIN needs an ON clause to be valid
  • Claims CROSS JOIN drops rows that have no match
  • Says an empty table still yields the other table's rows
  • Believes CROSS JOIN removes duplicate pairings automatically

context

resolving links…

open as a page

Why does SELECT * FROM orders o, customers c WHERE o.total > 100 return millions of rows?

level: middleimportance: must knowfreq 68%

basics

~20 s

The comma in the FROM list relates nothing, so the query is an accidental Cartesian product. The WHERE clause filters only orders, then every surviving order is paired with every customer: the result is (matching orders) times (customers) rows.

open as a page

Is FROM a CROSS JOIN b WHERE a.id = b.a_id equivalent to FROM a INNER JOIN b ON a.id = b.a_id?

level: middleimportance: should knowfreq 46%

basics

~20 s

Yes for inner joins: the standard defines an inner join as the Cartesian product filtered by the join predicate, so both forms return the same rows. CROSS JOIN accepts no ON clause of its own, and the explicit INNER JOIN form reads better.

open as a page

How do you use CROSS JOIN to report zero-sale days for every product in a date range?

level: seniorimportance: should knowfreq 40%

basics

~20 s

CROSS JOIN a date list to the product list to manufacture every (day, product) pair, LEFT JOIN the sales table on both keys, then COALESCE the aggregate to 0 so pairs with no sale still report a row.

open as a page

Why would you CROSS JOIN a table to a one-row subquery such as (SELECT SUM(amount) AS total FROM sales)?

level: middleimportance: nice to knowfreq 30%

basics

~20 s

Pairing N rows with a one-row subquery yields N rows, attaching that subquery's value — a grand total, say — to every row, so each row can be compared against it without repeating the aggregate query in the select list.

open as a page