> ## 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 INNER JOIN > How the MySQL INNER JOIN clause matches rows across 2 or more tables, with ON and USING, and why the row count grows when one row matches many. All examples on this page work on VillageSQL. Install Now → `INNER JOIN` keeps a row only when the match succeeds. It is the join you want whenever a row in one table points at a row in another and you want both. Open the client with `mysql -u root -p sakila` before you start. ## How INNER JOIN works ```sql theme={null} SELECT COLUMN_LIST FROM LEFT_TABLE INNER 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 them. The `ON` clause says which rows belong together. MySQL takes each row of the left table, finds the rows of the right table where that condition holds, and returns 1 combined row for each match. A row that finds nothing is dropped, on either side. [LEFT JOIN](/docs/tutorial/left-join) is the clause that keeps it instead. ## Examples ### 1) Join 2 tables `film.language_id` points at `language.language_id`. Match that pair and you can print the language name beside the title. ```sql theme={null} SELECT f.title, l.name FROM film AS f INNER JOIN language AS l ON f.language_id = l.language_id ORDER BY f.title; ``` ```text theme={null} +-----------------------------+---------+ | title | name | +-----------------------------+---------+ | ACADEMY DINOSAUR | English | | ACE GOLDFINGER | English | | ADAPTATION HOLES | English | ... +-----------------------------+---------+ 1000 rows in set (0.00 sec) ``` 1,000 rows, the same count as `film` on its own. Every film matched exactly 1 language, which is what you get when the joining column points at a single row. ### 2) Join across a foreign key Customers keep their street address in a separate table. ```sql theme={null} SELECT c.first_name, c.last_name, a.address FROM customer AS c INNER JOIN address AS a ON c.address_id = a.address_id ORDER BY c.last_name, c.first_name; ``` ```text theme={null} +-------------+--------------+----------------------------------------+ | first_name | last_name | address | +-------------+--------------+----------------------------------------+ | RAFAEL | ABNEY | 48 Maracaíbo Place | | NATHANIEL | ADAM | 786 Matsue Way | | KATHLEEN | ADAMS | 334 Munger (Monghyr) Lane | ... +-------------+--------------+----------------------------------------+ 599 rows in set (0.00 sec) ``` 599 rows, 1 per customer. ### 3) Join 3 tables Add another `INNER JOIN` clause for each further table. Sakila splits location across `address`, `city` and `country`, so reaching the city name takes 2 hops. ```sql theme={null} SELECT c.first_name, c.last_name, ci.city FROM customer AS c INNER JOIN address AS a ON c.address_id = a.address_id INNER JOIN city AS ci ON a.city_id = ci.city_id ORDER BY c.last_name, c.first_name; ``` ```text theme={null} +-------------+--------------+----------------------------+ | first_name | last_name | city | +-------------+--------------+----------------------------+ | RAFAEL | ABNEY | Talavera | | NATHANIEL | ADAM | Joliet | | KATHLEEN | ADAMS | Arak | ... +-------------+--------------+----------------------------+ 599 rows in set (0.00 sec) ``` Still 599 rows. Each join matched a single row, so the count did not move. ### 4) Shorten it with USING When the 2 columns carry the same name, `USING` replaces `ON` and says the name once. ```sql theme={null} SELECT f.title, l.name FROM film AS f INNER JOIN language AS l USING (language_id) ORDER BY f.title; ``` ```text theme={null} +-----------------------------+---------+ | title | name | +-----------------------------+---------+ | ACADEMY DINOSAUR | English | | ACE GOLDFINGER | English | | ADAPTATION HOLES | English | ... +-----------------------------+---------+ 1000 rows in set (0.00 sec) ``` The same 1,000 rows as example 1. `USING` works only when the names match on both sides, so `ON` is the form that always applies. ## Matching several rows multiplies them A join returns 1 row per match, so a row that matches many rows comes back many times. Each film has several actors. ```sql theme={null} SELECT f.title, fa.actor_id FROM film AS f INNER JOIN film_actor AS fa ON f.film_id = fa.film_id ORDER BY f.title, fa.actor_id; ``` ```text theme={null} +-----------------------------+----------+ | title | actor_id | +-----------------------------+----------+ | ACADEMY DINOSAUR | 1 | | ACADEMY DINOSAUR | 10 | | ACADEMY DINOSAUR | 20 | ... +-----------------------------+----------+ 5462 rows in set (0.00 sec) ``` 5,462 rows from 1,000 films, with each title repeated once per actor. This is the usual reason a join returns more rows than you expected, and it is the reason a total computed over a join can come out too high. ## 2 things that catch people out **An unmatched row is gone, not blank.** Start from `language` instead of `film` and you still get 1,000 rows, not 1,005: Sakila's other 5 languages match no film and drop out. An inner join reports what matched, never what is missing. [LEFT JOIN](/docs/tutorial/left-join) is how you ask the other question. **A missing `ON` is not an error.** MySQL reads `INNER JOIN` with no `ON` as every combination of both tables, so a typo returns 1,000 times 6 rows rather than a complaint. [CROSS JOIN](/docs/tutorial/cross-join) covers that, deliberate and accidental. ## Summary * Use `INNER JOIN` with `ON` to combine rows from tables that match. * Add 1 `INNER JOIN` clause per further table. * Expect unmatched rows to vanish, because an inner join reports only matches. * Expect the row count to grow when 1 row matches many. * Use `USING` only when the column has the same name on both sides. ## See also * [How joins work](/docs/tutorial/joins) — what separates this from the other kinds * [LEFT JOIN](/docs/tutorial/left-join) — keeping the rows this one drops * [MySQL JOIN performance and common mistakes](/docs/guides/joins) — indexing a join, and the row counts that come out wrong * [Foreign keys in MySQL](/docs/guides/foreign-keys) — the relationships these joins follow