SQL CROSS JOIN: Every Combination of Two Tables

A CROSS JOIN pairs every row of one table with every row of another. There is no ON condition, because nothing needs to match. The result is called the Cartesian product: if one table has 3 rows and the other has 2, you get 3 × 2 = 6 rows.

It is the least used JOIN, but a handful of real problems are solved by it neatly.

Syntax#

SELECT columns
FROM table_a
CROSS JOIN table_b;

Example: every size and colour#

A clothing shop sells each T-shirt in every combination of size and colour:

sizes

colors

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c
ORDER BY c.color, s.size DESC;

size | color
-----+------
S    | Black
M    | Black
L    | Black
S    | White
M    | White
L    | White

3 sizes × 2 colours = 6 variants. (ORDER BY s.size DESC happens to give S, M, L because it sorts the letters alphabetically in reverse.)

You can build values from the combinations, such as product codes:

SELECT c.color || '-' || s.size AS sku
FROM colors c
CROSS JOIN sizes s
ORDER BY sku;

sku
-------
Black-L
Black-M
Black-S
White-L
White-M
White-S

Row counts grow fast#

The size of a cross join is the product of the two row counts:

SELECT COUNT(*) AS combinations
FROM customers
CROSS JOIN orders;

combinations
------------
16

4 customers × 4 orders = 16 rows, most of them meaningless pairs. With 10,000 customers and 100,000 orders it would be one billion rows.

A real use: filling gaps in reports#

Reports often need a row for every combination, even when there is no data. Suppose monthly_sales has no rows for May yet, but the report must show every region for April and May. Cross join regions with months, then LEFT JOIN the real data:

SELECT r.region,
       m.month,
       COALESCE(s.revenue, 0) AS revenue
FROM (SELECT DISTINCT region FROM monthly_sales) r
CROSS JOIN (SELECT '2026-04' AS month UNION ALL SELECT '2026-05') m
LEFT JOIN monthly_sales s
  ON s.region = r.region
 AND s.month = m.month
ORDER BY r.region, m.month;

region | month   | revenue
-------+---------+--------
North  | 2026-04 | 170
North  | 2026-05 | 0
South  | 2026-04 | 125
South  | 2026-05 | 0

Without the cross join, May would simply be missing from the report instead of showing zeros.

Accidental cross joins#

The old comma syntax creates a cross join if you forget the condition:

-- Cross join: every customer paired with every order
SELECT *
FROM customers, orders;

-- Old-style inner join: the condition lives in WHERE
SELECT *
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;

The same happens in MySQL if you write JOIN without ON. Writing explicit JOIN ... ON (and CROSS JOIN when you really mean it) makes your intention obvious and these mistakes easy to spot.

Common mistakes#

Practice#

SELECT s.name AS starter,
       m.name AS main
FROM starters s
CROSS JOIN mains m
ORDER BY s.name, m.name;

Key takeaway#