CROSS JOIN usage and examples

Overview

CROSS JOIN pairs every left-side row with every right-side row, so the result size is the product of both inputs. It is useful for complete grids such as date × region or product × option, but grows rapidly on large inputs. If the intent is key-based matching, a missing ON condition is usually a defect.

Basic syntax

SELECT a.name, b.name FROM table_a a CROSS JOIN table_b b;

Examples

CROSS JOIN example

The example combines two rows from table_a with two rows from table_b to produce four rows, making the Cartesian product explicit.

Sample table

Table：table_a

table_a
name
A
B

Table：table_b

table_b
name
X
Y

SQL

SELECT a.name AS a_name, b.name AS b_name FROM table_a a CROSS JOIN table_b b;

Run result

result
a_name	b_name
A	X
A	Y
B	X
B	Y

Common issues

How many rows does CROSS JOIN return?

Without a later filter, row count is the product of both inputs; 100 by 1,000 rows produces 100,000 rows.

When is CROSS JOIN intentional?

It is useful for complete date-store grids, test data, or all candidate combinations, but estimate the result size first.

How can I control Cartesian-product cost?

Filter both inputs in subqueries or CTEs, select only necessary columns, and validate estimated row counts on sample data.

Keep reading

INNER JOIN LEFT JOIN SELF JOIN FULL OUTER JOIN

CROSS JOIN