Understanding SQL CROSS JOIN with Examples

Estimated study time: 7 minutes. Every row from one table paired with every row from another.

A CROSS JOIN returns the Cartesian product of two tables — every row from the first table is combined with every row from the second table. Unlike other joins, it doesn't need a join condition.

Basic Syntax

SELECT *
FROM TableA
CROSS JOIN TableB;

Example

SELECT Colors.ColorName, Sizes.SizeLabel
FROM Colors
CROSS JOIN Sizes;

If Colors has 3 rows and Sizes has 4 rows, this query returns 12 rows — one row for every possible color-size combination. That kind of combination generator is exactly what CROSS JOIN is built for, such as producing every variant of a product.

Old-Style Syntax

A comma-separated FROM clause with no WHERE condition produces the same result as an explicit CROSS JOIN, though the explicit syntax is preferred because it makes the intent clear.

SELECT *
FROM Colors, Sizes;

CROSS JOIN with a WHERE Clause

Adding a WHERE clause after a CROSS JOIN effectively turns it into an INNER JOIN, since the filter removes the unmatched combinations.

SELECT *
FROM Employees
CROSS JOIN Departments
WHERE Employees.DeptID = Departments.DeptID;

⚠️ Caution: CROSS JOIN row counts multiply, not add. Joining two 10,000-row tables produces 100 million rows — use it deliberately, not by accident from a missing join condition.

When to Use CROSS JOIN