SQL CROSS JOIN: What It Is and When to Use It

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

A SQL CROSS JOIN returns every possible pairing of rows from two tables. If the first table has 3 rows and the second has 4, you get 12 rows. There is no matching key and no filter, so the result is the full Cartesian product [1].

Quick Answer

A CROSS JOIN combines each row from the first table with each row from the second table, with no join condition [1].
The row count is the product of the two table sizes: $m \times n$ rows.
Unlike an inner or outer join, it takes no ON or USING clause [2].
You can filter the result with WHERE, which turns it into something close to an inner join [1].
Real uses include building combination grids, generating date scaffolds, and stress-testing a server [1].

What CROSS JOIN Means

In plain terms, a CROSS JOIN takes two tables and produces every combination of their rows. Nothing is matched and nothing is dropped. Every row on the left appears once with every row on the right.

The precise definition is the Cartesian product of two relations. If table A has $m$ rows and table B has $n$ rows, the CROSS JOIN of A and B is a relation with $m \times n$ rows, where each output row is a concatenation of one A row and one B row [1]. This is the same result you get by listing both tables in a FROM clause separated by a comma, and it is equivalent to an inner join with an always-true condition such as ON 1 = 1 [1][2].

Because there is no predicate, a CROSS JOIN does not remove any rows. That is the key difference from every other join type. An inner join keeps only rows that satisfy a condition. A CROSS JOIN keeps everything and lets you decide what to do with it afterward.

How It Works

The mechanism is a nested loop over both tables. For each row in the left table, the engine pairs it with every row in the right table, then moves to the next left row.

The size of the result follows one formula:

$$R = m \times n$$

$m$ is the number of rows in the first table.
$n$ is the number of rows in the second table.
$R$ is the number of rows in the result.

Three tables multiply the same way. A CROSS JOIN of tables with 3, 4, and 5 rows produces $3 \times 4 \times 5 = 60$ rows. This growth is why a CROSS JOIN on large tables can produce an enormous result very quickly.

Most database engines execute a CROSS JOIN with a nested loop join, one of the three fundamental binary join algorithms alongside sort-merge and hash join [1]. There is no key to hash or sort on, so the loop is the natural choice.

The syntax is short. You write FROM table_a CROSS JOIN table_b, and the standard does not allow an ON or USING clause on a cross join [2]. In the SQL:2011 standard, cross joins belong to the optional F401 "Extended joined table" package, so support is widespread but technically optional [1].

Worked Example

This example uses two small lookup tables, sizes and colors, to build a full grid of product variations.

The input table sizes:

size_id	size_label
1	Small
2	Medium
3	Large

The colors table holds four rows: Red, Green, Blue, and Black.

SELECT
  sizes.size_label,
  colors.color_name
FROM sizes
CROSS JOIN colors
ORDER BY
  sizes.size_id,
  colors.color_id;

The steps are straightforward. Start with the 3-row sizes table. CROSS JOIN colors pairs every size row with every color row, producing $3 \times 4 = 12$ combinations. The SELECT returns the two columns that describe each combination. The ORDER BY sorts the 12 rows by size then color for readability.

The result:

size_label	color_name
Small	Red
Small	Green
Small	Blue
Small	Black
Medium	Red
Medium	Green
Medium	Blue
Medium	Black
Large	Red
Large	Green
Large	Blue
Large	Black

The result was checked with an equivalent SQLite query, executed in sqlite3 3.37.2. Every size appears exactly four times, and every color appears exactly three times. That balance is the signature of a clean Cartesian product.

How to Interpret It

Read a CROSS JOIN result as a complete grid of possibilities, not as matched records. Each row answers the question "what happens if I combine this left item with this right item?"

Two checks confirm the output is correct. First, the row count should equal the product of the input row counts. Second, each distinct value from the left table should appear exactly $n$ times, and each distinct value from the right table should appear exactly $m$ times. In the example, each size appears 4 times and each color appears 3 times.

If you add a WHERE clause, you are filtering that grid. A filter like WHERE colors.color_name = 'Red' cuts the 12 rows down to 3. At that point the query behaves like an inner join, because you have effectively supplied the missing predicate [1]. This is a useful mental model. A CROSS JOIN plus a filter is an inner join written in two steps.

When to Use It (and when not to)

Use a CROSS JOIN when you genuinely need every combination. Common cases:

Building variation grids. A product table crossed with a color table produces every sellable variant, which is exactly the pattern Microsoft documents for Power Query [3].
Generating date scaffolds. Cross a list of dates with a list of categories so every category has a row for every date, including days with no activity.
Creating test data. Cross small dimension tables to produce a larger set of synthetic rows.
Checking server performance. Wikipedia lists performance testing as a normal use of cross joins [1].

Avoid a CROSS JOIN when you actually want matched rows. If two tables share a key and you want related records, you want an inner join or a LEFT JOIN. A CROSS JOIN on two large tables can produce billions of rows and exhaust memory or disk before you see a single result.

A practical middle ground is to cross join small tables and filter immediately. If you need combinations only for a subset of rows, put the subset in a subquery or a common table expression first, then cross join the smaller result.

CROSS JOIN vs INNER JOIN

The closest related idea is the inner join. Both return combinations of rows, but they differ in whether a condition is required.

Feature	CROSS JOIN	INNER JOIN
Join condition	None allowed [2]	Required (`ON` or `USING`)
Rows returned	$m \times n$, all combinations	Only rows matching the condition
Filtering	Optional, via `WHERE`	Built into the join condition
Typical use	Combination grids, scaffolds	Relating records by key
Equivalent form	`INNER JOIN ... ON 1 = 1` [1]	`CROSS JOIN` plus a `WHERE` filter

The two are connected. A CROSS JOIN with a WHERE clause that compares columns produces the same rows as an inner join with that comparison in the ON clause [1]. The difference is readability and intent. When you write CROSS JOIN, you are telling the reader that every pairing is meaningful.

Common Mistakes

Forgetting the row explosion. Two tables with 10,000 rows each produce 100 million rows. Fix: estimate $m \times n$ before running the query, and filter or limit early.
Adding an ON clause to a CROSS JOIN. The standard does not allow it [2]. Fix: move the condition to WHERE, or switch to an inner join.
Using a CROSS JOIN when you meant an inner join. If the tables share a key, a missing join condition silently multiplies rows. Fix: check whether a key relationship exists before choosing the join type.
Assuming the result order is stable. Without ORDER BY, row order is not guaranteed. Fix: add an explicit ORDER BY when order matters.
Cross joining before filtering. Crossing two full tables and then filtering wastes work. Fix: filter each side first with a subquery or CTE, then cross join the smaller results.
Ignoring duplicate keys in the source. If a table has duplicate rows, the product grows accordingly. Fix: deduplicate with DISTINCT before joining when duplicates are not meaningful.

Limitations

A CROSS JOIN cannot express a relationship between tables. It has no concept of matching, so it cannot tell you which rows belong together. If your data has a foreign key, a CROSS JOIN ignores it entirely and produces pairs that may be meaningless.

The other limitation is scale. Because the output grows multiplicatively, a CROSS JOIN is only practical when at least one side is small. On large tables it can consume enormous memory and time, and the result is often too big to inspect. Treat the row count formula as a hard budget check before you run anything.

Frequently Asked Questions

What is a cross join in SQL?

A cross join is a join that returns the Cartesian product of two tables. It pairs every row from the first table with every row from the second table, with no matching condition [1]. The result has $m \times n$ rows.

Does a CROSS JOIN need an ON clause?

No. A CROSS JOIN takes no ON or USING clause, and the standard does not permit one [2]. If you need a condition, put it in a WHERE clause or use an inner join instead.

Is a CROSS JOIN the same as a comma join?

Yes, functionally. Listing two tables in FROM separated by a comma produces the same Cartesian product as an explicit CROSS JOIN [2]. The explicit keyword is clearer about intent.

Can I filter a CROSS JOIN?

Yes. Add a WHERE clause after the join. Filtering a CROSS JOIN can produce the equivalent of an inner join, because the filter supplies the predicate the join itself lacks [1].

When should I avoid a CROSS JOIN?

Avoid it when the tables share a key and you want related rows, or when both tables are large. In those cases an inner join, a LEFT JOIN, or a filtered subquery will be faster and more accurate.