> ## 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. # How MySQL joins work > Why data is split across tables in MySQL, what a join does to put it back together, and what separates an inner join from an outer join and a cross join. All examples on this page work on VillageSQL. Install Now → Every query so far read 1 table. Real databases spread their information over many, and a join is how you read across them. This lesson explains what a join does and what the different kinds are for. The lessons after it take each kind in turn. Open the client with `mysql -u root -p sakila` before you start. ## Why the data is split up Sakila records the language of each film as a number. ```sql theme={null} SELECT film_id, title, language_id FROM film ORDER BY film_id LIMIT 3; ``` ```text theme={null} +---------+------------------+-------------+ | film_id | title | language_id | +---------+------------------+-------------+ | 1 | ACADEMY DINOSAUR | 1 | | 2 | ACE GOLDFINGER | 1 | | 3 | ADAPTATION HOLES | 1 | +---------+------------------+-------------+ 3 rows in set (0.00 sec) ``` The name that goes with the number lives in its own table. ```sql theme={null} SELECT language_id, name FROM language ORDER BY language_id; ``` ```text theme={null} +-------------+----------+ | language_id | name | +-------------+----------+ | 1 | English | | 2 | Italian | | 3 | Japanese | | 4 | Mandarin | | 5 | French | | 6 | German | +-------------+----------+ 6 rows in set (0.00 sec) ``` Storing the word `English` once and pointing at it 1,000 times takes less room than storing it 1,000 times, and correcting a spelling means changing 1 row rather than 1,000. The cost is that a query wanting the title and the language name has to read both tables. ## How a join works ```sql theme={null} SELECT COLUMN_LIST FROM LEFT_TABLE JOIN RIGHT_TABLE ON LEFT_TABLE.COLUMN_NAME = RIGHT_TABLE.COLUMN_NAME; ``` Replace `COLUMN_LIST` with the columns you want, `LEFT_TABLE` with the table you start from, `RIGHT_TABLE` with the one you are pulling extra columns out of, and each `COLUMN_NAME` with the column on that side that links the 2 tables. The server takes each row of the left table, finds the rows of the right table where the `ON` condition is true, and returns 1 combined row per match. ## Examples ### 1) Read the name instead of the number ```sql theme={null} SELECT f.title, l.name FROM film AS f JOIN language AS l ON f.language_id = l.language_id ORDER BY f.title LIMIT 3; ``` ```text theme={null} +------------------+---------+ | title | name | +------------------+---------+ | ACADEMY DINOSAUR | English | | ACE GOLDFINGER | English | | ADAPTATION HOLES | English | +------------------+---------+ 3 rows in set (0.00 sec) ``` `AS f` and `AS l` are table aliases, which the [next lesson](/docs/tutorial/table-aliases) covers. ## What the kinds of join are for They differ in 1 thing: what happens to a row that finds no match. | Join | Keeps | | - | - | | `INNER JOIN` | only the rows that found a match on both sides | | `LEFT JOIN` | every row of the left table, matched or not | | `RIGHT JOIN` | every row of the right table, matched or not | | `CROSS JOIN` | every row of the left paired with every row of the right | An unmatched row kept by an outer join still has to fill the columns it has no values for, and it fills them with NULL. That is why [IS NULL](/docs/tutorial/is-null) turns up so often beside a `LEFT JOIN`: it is how you ask for the rows that matched nothing. `CROSS JOIN` is the odd one out, because it matches nothing. It is the one you ask for deliberately when you want every combination, and the one you get by accident when you leave an `ON` clause out. ## The condition matters more than the keyword `JOIN`, `INNER JOIN` and `CROSS JOIN` are 1 thing in MySQL, and the grammar takes an `ON` clause on any of them or none. So the keyword you type does not decide your result. The presence of an `ON` condition does: with one you get matches, without one you get every combination, whichever of the 3 words you wrote. Because the server will not tell them apart for you, write `INNER JOIN ... ON` when you mean a match and `CROSS JOIN` with no `ON` when you mean every combination. The keyword is then a note to the next reader rather than an instruction to the server. ## Summary * Expect information to be split across tables, with 1 table pointing at another by id. * Use a join to read across those tables in 1 query. * Read the `ON` condition as the rule for which rows belong together. * Pick the kind of join by what should happen to a row that matches nothing. * Expect NULL in the columns of a row an outer join kept without a match. ## See also * [Table aliases](/docs/tutorial/table-aliases) and [INNER JOIN](/docs/tutorial/inner-join) — the short names, and the join that keeps only matches * [LEFT JOIN](/docs/tutorial/left-join) and [RIGHT JOIN](/docs/tutorial/right-join) — the joins that keep unmatched rows * [CROSS JOIN](/docs/tutorial/cross-join) and [self join](/docs/tutorial/self-join) — every combination, and a table joined to itself * [MySQL JOIN performance and common mistakes](/docs/guides/joins) — what goes wrong once the tables are large