> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL CROSS JOIN > How a MySQL CROSS JOIN pairs every row of one table with every row of another, how many rows that produces, and how to tell a deliberate one from an accident. 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 ```sql theme={null} 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. ```sql theme={null} SELECT l.name, s.store_id FROM language AS l CROSS JOIN store AS s ORDER BY l.name, s.store_id; ``` ```text theme={null} +----------+----------+ | 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 ```sql theme={null} SELECT l.name, s.store_id FROM language AS l, store AS s ORDER BY l.name, s.store_id; ``` ```text theme={null} +----------+----------+ | 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. ```sql theme={null} SELECT COUNT(*) FROM film f, language l JOIN store s ON s.store_id = f.language_id; ``` ```text theme={null} 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. ```sql theme={null} SELECT COUNT(*) AS rows_returned FROM film AS f CROSS JOIN language AS l ON f.language_id = l.language_id; ``` ```text theme={null} +---------------+ | 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. ```sql theme={null} SELECT f.title, l.name FROM film AS f CROSS JOIN language AS l ORDER BY f.title, l.name; ``` ```text theme={null} +-----------------------------+----------+ | 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](/docs/tutorial/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. ## See also * [How joins work](/docs/tutorial/joins) — where this sits among the other kinds * [INNER JOIN](/docs/tutorial/inner-join) — the join that matches rows instead * [Table aliases](/docs/tutorial/table-aliases) — the short names these queries use * [MySQL JOIN performance and common mistakes](/docs/guides/joins) — the accident, seen from the other end