MySQL CROSS JOIN

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

A CROSS JOIN pairs every row of one table with every row of another. You write no ON condition, because nothing is being matched. It is the join you ask for when you want every combination, and the one you get by accident when you leave an ON clause out. Open the client with mysql -u root -p sakila before you start.

How CROSS JOIN works

SELECT COLUMN_LIST
FROM FIRST_TABLE
CROSS JOIN SECOND_TABLE;

Replace COLUMN_LIST with the columns you want, and FIRST_TABLE and SECOND_TABLE with the tables to combine. The result holds 1 row for every possible pairing, so its size is the first table’s row count multiplied by the second’s. 6 rows crossed with 2 rows give 12.

Examples

1) Every combination

Sakila has 6 languages and 2 stores.

SELECT l.name, s.store_id
FROM language AS l
CROSS JOIN store AS s
ORDER BY l.name, s.store_id;

+----------+----------+
| name     | store_id |
+----------+----------+
| English  |        1 |
| English  |        2 |
| French   |        1 |
| French   |        2 |
| German   |        1 |
| German   |        2 |
| Italian  |        1 |
| Italian  |        2 |
| Japanese |        1 |
| Japanese |        2 |
| Mandarin |        1 |
| Mandarin |        2 |
+----------+----------+
12 rows in set (0.00 sec)

Every language appears once per store. Nothing here says a store stocks films in that language: the rows are combinations, not facts about the data. That is the point of the query. A grid like this is a starting shape when you want a row for every pairing whether or not anything happened for it.

2) The comma is the older spelling

SELECT l.name, s.store_id
FROM language AS l, store AS s
ORDER BY l.name, s.store_id;

+----------+----------+
| name     | store_id |
+----------+----------+
| English  |        1 |
| English  |        2 |
| French   |        1 |
| French   |        2 |
| German   |        1 |
| German   |        2 |
| Italian  |        1 |
| Italian  |        2 |
| Japanese |        1 |
| Japanese |        2 |
| Mandarin |        1 |
| Mandarin |        2 |
+----------+----------+
12 rows in set (0.00 sec)

The same 12 rows. A comma between 2 tables in FROM is a cross join. You will meet it in older SQL, often with the matching condition down in WHERE, which works and hides what kind of join you are reading. A comma binds more loosely than JOIN, so an ON clause cannot reach a table on the other side of a comma. The 2 spellings do appear together in real queries, and this is what goes wrong when they do.

SELECT COUNT(*) FROM film f, language l JOIN store s ON s.store_id = f.language_id;

ERROR 1054 (42S22): Unknown column 'f.language_id' in 'on clause'

Write CROSS JOIN when you mean one, and keep to a single spelling per query. The keyword does not enforce the rule either. MySQL accepts an ON clause on a CROSS JOIN and then behaves as an INNER JOIN, so the word in the query is not proof of the join you got.

SELECT COUNT(*) AS rows_returned
FROM film AS f CROSS JOIN language AS l ON f.language_id = l.language_id;

+---------------+
| rows_returned |
+---------------+
|          1000 |
+---------------+
1 row in set (0.00 sec)

1,000 rather than 6,000: every film matched to its own language. Read the ON clause, not the keyword, to know what a join does.

The number gets large quickly

12 rows is harmless. The same operation on the tables you usually query is not.

SELECT f.title, l.name
FROM film AS f
CROSS JOIN language AS l
ORDER BY f.title, l.name;

+-----------------------------+----------+
| title                       | name     |
+-----------------------------+----------+
| ACADEMY DINOSAUR            | English  |
| ACADEMY DINOSAUR            | French   |
| ACADEMY DINOSAUR            | German   |
...
+-----------------------------+----------+
6000 rows in set (0.01 sec)

1,000 films times 6 languages is 6,000 rows, and each film now claims all 6 languages. Cross 2 tables of 10,000 rows each and the answer has 100 million rows, which is how a query that should have been instant runs until somebody kills it. MySQL accepts INNER JOIN with no ON clause and treats it as this. So when a join returns a suspiciously round and suspiciously large number, a missing ON is the first thing to check. The INNER JOIN lesson shows that accident.

Summary

Use CROSS JOIN to pair every row of one table with every row of another.
Write no ON clause, because nothing is being matched.
Expect MySQL to accept CROSS JOIN ... ON anyway, and to treat it as an inner join.
Multiply the 2 row counts to know the size before you run it.
Read a comma between tables in FROM as a cross join, and keep to 1 spelling per query.
Suspect a missing ON when a join returns a round, large number of rows.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How CROSS JOIN works

Examples

1) Every combination

2) The comma is the older spelling

The number gets large quickly

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How CROSS JOIN works

​Examples

​1) Every combination

​2) The comma is the older spelling

​The number gets large quickly

​Summary

​See also

How CROSS JOIN works

Examples

1) Every combination

2) The comma is the older spelling

The number gets large quickly

Summary

See also