MySQL – CROSS JOIN

A CROSS JOIN pairs every row on the left with every row on the right. There is no ON clause, because nothing is being matched — the result is the Cartesian product, and its size is simply one count multiplied by the other.

The arithmetic

SELECT COUNT(*) AS pairs FROM product p CROSS JOIN crust c;

+-------+
| pairs |
+-------+
|    56 |
+-------+

14 products, 4 crusts, 56 rows:

SELECT COUNT(*) AS products, (SELECT COUNT(*) FROM crust) AS crusts, COUNT(*) * (SELECT COUNT(*) FROM crust) AS product_times_crust FROM product;

+----------+--------+---------------------+
| products | crusts | product_times_crust |
+----------+--------+---------------------+
|       14 |      4 |                  56 |
+----------+--------+---------------------+

That multiplication is why an accidental cross join is expensive. Two tables of 400,000 rows produce 160 billion rows — a query that will not finish, on tables that are individually unremarkable.

The accidental one

A comma join with no WHERE is a cross join:

-- these two are the same query
SELECT p.name, c.name FROM product p, crust c;
SELECT p.name, c.name FROM product p CROSS JOIN crust c;

So is a comma join whose WHERE forgot one of its conditions — join three tables and supply two conditions instead of three, and one table joins to everything. Nothing errors; the query is simply enormous and the totals are silently multiplied.

This is the strongest practical argument for JOIN ... ON over the comma form. With explicit syntax, a missing relationship is a missing ON clause, which is a syntax error. With commas it is a valid query with a wrong answer. See INNER JOIN.

When you actually want one

The legitimate use is generating a complete grid — every combination that should exist, so you can compare it against what does. The pizza menu is priced per product and size, and a missing price row means a size a customer cannot order:

SELECT p.name, s.size
FROM   product p
CROSS  JOIN (SELECT 'SMALL' AS size UNION ALL SELECT 'MEDIUM' UNION ALL SELECT 'LARGE') s
WHERE  p.type = 'DRINK'
ORDER  BY p.id, s.size
LIMIT  6;

+------------+--------+
| name       | size   |
+------------+--------+
| Pepsi      | LARGE  |
| Pepsi      | MEDIUM |
| Pepsi      | SMALL  |
| Diet Pepsi | LARGE  |
| Diet Pepsi | MEDIUM |
| Diet Pepsi | SMALL  |
+------------+--------+

The derived table on the right supplies the three sizes as rows. Left-join that grid to the real prices and the gaps show up as NULLs:

SELECT p.name, s.size
FROM   product p
CROSS  JOIN (SELECT 'SMALL' AS size UNION ALL SELECT 'MEDIUM' UNION ALL SELECT 'LARGE') s
LEFT   JOIN product_size ps ON ps.product_id = p.id AND ps.size = s.size
WHERE  ps.id IS NULL;

It returns nothing on this menu — all 14 products are priced in all three sizes, which is the answer you want a data-quality check to give. The same shape covers a report that must show every month even when a month had no orders: cross join the months against the categories, then left join the totals.

CROSS JOIN with a condition

MySQL accepts CROSS JOIN ... ON and treats it as an inner join — CROSS, INNER and plain JOIN are synonyms to the parser, and only the presence of ON decides the behaviour. Do not rely on that. Write CROSS JOIN when you mean every combination and JOIN ... ON when you mean matching rows, so the intent is legible without counting clauses.

What to remember

• CROSS JOIN takes no ON; the row count is left × right.
• A comma join with a missing condition is an accidental cross join that does not error.
• Its real use is generating a full grid to find what is missing from it.
• Say CROSS JOIN deliberately, so a reader knows it was not a mistake.