CROSS JOIN

A CROSS JOIN pairs every row from one table with every row from another, producing what is called the Cartesian product of the two tables. Unlike every other join type covered in this section, a CROSS JOIN has no ON clause at all — there is no matching condition, because every possible pairing is included by definition. If table A has 4 rows and table B has 5 rows, a CROSS JOIN between them always produces exactly 4 x 5 = 20 rows.

Basic syntax

CROSS JOIN syntax

SQL

SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;

With 4 customers and 5 orders in the reference tables, this query returns 20 rows — every customer paired with every order, whether or not that customer actually placed it. That is rarely what you want with transactional data like customers and orders, which is exactly why CROSS JOIN is more useful for generating combinations than for querying related records.

Use case: generating all combinations

CROSS JOIN shines when you genuinely need every combination of two independent sets — for example, every size paired with every color for a product catalog, or every day in a date range paired with every store location for a reporting template.

Generating every size/color product variant

SQL

CREATE TABLE sizes  (size  VARCHAR(10));
CREATE TABLE colors (color VARCHAR(10));

INSERT INTO sizes  (size)  VALUES ('S'), ('M'), ('L');
INSERT INTO colors (color) VALUES ('Red'), ('Blue');

SELECT size, color
FROM sizes
CROSS JOIN colors;

size | color
-----+------
S    | Red
S    | Blue
M    | Red
M    | Blue
L    | Red
L    | Blue

Six rows come out of three sizes and two colors (3 x 2), giving every variant combination in one query — far simpler than generating the same list by hand or in application code.

The classic "missing ON clause" bug

An accidental CROSS JOIN doesn't error, it just returns garbage

Older comma-separated join syntax makes it dangerously easy to write an accidental CROSS JOIN. Forgetting a WHERE condition that ties two tables together does not raise an error — it silently returns every combination of rows from both tables, which can look plausible at a glance (especially with small test data) while being completely wrong, and can be catastrophic on large tables where it multiplies row counts into the millions or billions.

A missing join condition silently becomes a CROSS JOIN

SQL

-- Bug: no WHERE clause linking customers to orders.
-- This does NOT error — it returns a Cartesian product (20 rows).
SELECT c.name, o.order_id, o.amount
FROM customers c, orders o;

-- Fixed: the WHERE clause restores the intended join condition.
SELECT c.name, o.order_id, o.amount
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;

This is one of the strongest arguments for always using explicit JOIN ... ON syntax instead of the older comma-separated FROM list: with explicit JOIN syntax, forgetting the ON clause is a syntax error you catch immediately, rather than a silent correctness bug you might not notice until the numbers in a report look wrong.

CROSS JOIN produces the Cartesian product: every row from A paired with every row from B.
It has no ON clause, because there is no filtering condition — everything is included.
Useful for generating combinations, such as size/color variants or a date-dimension table.
Forgetting a join condition with old-style comma joins silently produces an accidental CROSS JOIN instead of an error.