MySQL INNER JOIN

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

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

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 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.

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;

+-----------------------------+---------+
| 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.

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;

+-------------+--------------+----------------------------------------+
| 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.

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;

+-------------+--------------+----------------------------+
| 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.

SELECT f.title, l.name
FROM film AS f
INNER JOIN language AS l USING (language_id)
ORDER BY f.title;

+-----------------------------+---------+
| 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.

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;

+-----------------------------+----------+
| 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 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 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.

Getting Started

Server Development

Using Extensions

Creating Extensions

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

How INNER JOIN works

Examples

1) Join 2 tables

2) Join across a foreign key

3) Join 3 tables

4) Shorten it with USING

Matching several rows multiplies them

2 things that catch people out

Summary

See also

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

​How INNER JOIN works

​Examples

​1) Join 2 tables

​2) Join across a foreign key

​3) Join 3 tables

​4) Shorten it with USING

​Matching several rows multiplies them

​2 things that catch people out

​Summary

​See also

How INNER JOIN works

Examples

1) Join 2 tables

2) Join across a foreign key

3) Join 3 tables

4) Shorten it with USING

Matching several rows multiplies them

2 things that catch people out

Summary

See also