How MySQL joins work

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

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.

SELECT film_id, title, language_id FROM film ORDER BY film_id LIMIT 3;

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

SELECT language_id, name FROM language ORDER BY language_id;

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

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

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;

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

Getting Started

Server Development

Using Extensions

Creating Extensions

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

Why the data is split up

How a join works

Examples

1) Read the name instead of the number

What the kinds of join are for

The condition matters more than the keyword

Summary

See also

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

​Why the data is split up

​How a join works

​Examples

​1) Read the name instead of the number

​What the kinds of join are for

​The condition matters more than the keyword

​Summary

​See also

Why the data is split up

How a join works

Examples

1) Read the name instead of the number

What the kinds of join are for

The condition matters more than the keyword

Summary

See also