MySQL CROSS JOIN: Every Combination of Two Tables

A CROSS JOIN pairs every row of one table with every row of the other. With 4 rows and 3 rows, you get 12. This is also called the Cartesian product. There is no join condition.

query.sql

SQL

CREATE TABLE sizes (size VARCHAR(2));
CREATE TABLE colors (color VARCHAR(10));
INSERT INTO sizes VALUES ('S'), ('M'), ('L'), ('XL');
INSERT INTO colors VALUES ('red'), ('blue'), ('black');

SELECT s.size, c.color, CONCAT(s.size, '-', c.color) AS sku_suffix
FROM sizes s
CROSS JOIN colors c
ORDER BY s.size, c.color;

Output

OUTPUT

+------+-------+------------+
| size | color | sku_suffix |
+------+-------+------------+
| L    | black | L-black    |
| L    | blue  | L-blue     |
| L    | red   | L-red      |
| M    | black | M-black    |
| M    | blue  | M-blue     |
| M    | red   | M-red      |
| S    | black | S-black    |
| S    | blue  | S-blue     |
| S    | red   | S-red      |
| XL   | black | XL-black   |
| XL   | blue  | XL-blue    |
| XL   | red   | XL-red     |
+------+-------+------------+
12 rows in set

Four sizes times three colours gives twelve product variants. This is a legitimate use: generate all combinations.

Useful cases

1. Product variants (as above): sizes by colours, or plans by billing periods.

2. A calendar grid, one row per day and per person:

query.sql

SQL

CREATE TABLE staff (name VARCHAR(10));
INSERT INTO staff VALUES ('Asha'), ('Ben');

SELECT st.name, d.day
FROM staff st
CROSS JOIN (SELECT DATE('2026-07-01') + INTERVAL n DAY AS day FROM (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2) AS x) AS d
ORDER BY st.name, d.day;

Output

OUTPUT

+------+------------+
| name | day        |
+------+------------+
| Asha | 2026-07-01 |
| Asha | 2026-07-02 |
| Asha | 2026-07-03 |
| Ben  | 2026-07-01 |
| Ben  | 2026-07-02 |
| Ben  | 2026-07-03 |
+------+------------+
6 rows in set

3. Filling in zeros. A report must show every region for every month, even when there were no sales. Cross join the list of regions with the list of months to make the skeleton, then LEFT JOIN the facts onto it:

query.sql

SQL

CREATE TABLE regions (region VARCHAR(10));
CREATE TABLE months (m CHAR(7));
CREATE TABLE sales (region VARCHAR(10), m CHAR(7), amount INT);
INSERT INTO regions VALUES ('north'), ('south');
INSERT INTO months VALUES ('2026-05'), ('2026-06');
INSERT INTO sales VALUES ('north', '2026-05', 100), ('north', '2026-06', 150), ('south', '2026-06', 80);

SELECT r.region, mo.m, COALESCE(SUM(s.amount), 0) AS amount
FROM regions r
CROSS JOIN months mo
LEFT JOIN sales s ON s.region = r.region AND s.m = mo.m
GROUP BY r.region, mo.m
ORDER BY r.region, mo.m;

Output

OUTPUT

+--------+---------+--------+
| region | m       | amount |
+--------+---------+--------+
| north  | 2026-05 |    100 |
| north  | 2026-06 |    150 |
| south  | 2026-05 |      0 |
| south  | 2026-06 |     80 |
+--------+---------+--------+
4 rows in set

South has no sales in May, and it still gets a row with 0.

4. Test data by combining small tables (Generating Test Data).

The size danger

The result has rows(A) x rows(B) rows. Two tables of 10,000 rows give 100,000,000 rows. A cross join on large tables can bring down a server.

The accidental cross join

You get one when you forget the join condition:

query.sql

SQL

CREATE TABLE people (id INT, name VARCHAR(10));
CREATE TABLE cities (id INT, city VARCHAR(10));
INSERT INTO people VALUES (1, 'Asha'), (2, 'Ben');
INSERT INTO cities VALUES (1, 'Pune'), (2, 'Delhi'), (3, 'Kochi');

SELECT COUNT(*) AS rows_returned FROM people, cities;

Output

OUTPUT

+---------------+
| rows_returned |
+---------------+
|             6 |
+---------------+
1 row in set

Six rows instead of two. A query that suddenly returns far too many rows, or runs for a very long time, often has a missing ON or WHERE.

In MySQL, JOIN without ON is allowed and means the same as CROSS JOIN. (Many other databases reject it.) That is one more reason to always write ON for an inner join and use the word CROSS JOIN when you mean it.

dangerous

Safe habit

When you write a join between big tables, run it first with LIMIT 10, and check EXPLAIN to see the estimated number of rows. A cross join shows a huge estimate.