MySQL LEFT JOIN

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

All examples on this page work on VillageSQL. Install Now →

An inner join reports what matched. A LEFT JOIN keeps every row of the left table whether it matched or not, which is how you answer the other question: what did not match. Open the client with mysql -u root -p sakila before you start.

How LEFT JOIN works

SELECT COLUMN_LIST
FROM LEFT_TABLE
LEFT 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 every row of which you want to keep, 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 server behaves as an inner join for the rows that match. For a left row that matches nothing, it keeps the row anyway and fills every column of the right table with NULL. LEFT OUTER JOIN is the same clause spelled out, and the word OUTER adds nothing.

Examples

1) Keep the unmatched rows

Sakila has 6 languages and every film is English, so 5 languages match no film. An inner join drops them. This does not.

SELECT l.name, f.title
FROM language AS l
LEFT JOIN film AS f ON l.language_id = f.language_id
ORDER BY l.name, f.title;

+----------+-----------------------------+
| name     | title                       |
+----------+-----------------------------+
| English  | ACADEMY DINOSAUR            |
| English  | ACE GOLDFINGER              |
| English  | ADAPTATION HOLES            |
...
+----------+-----------------------------+
1005 rows in set (0.00 sec)

1,005 rows: the 1,000 matches, plus 1 row for each of the 5 languages that matched nothing.

2) List only the rows that matched nothing

Add WHERE on a column of the right table that can never be NULL in a real match, and you have kept exactly the unmatched rows.

SELECT l.name, f.title
FROM language AS l
LEFT JOIN film AS f ON l.language_id = f.language_id
WHERE f.film_id IS NULL
ORDER BY l.name;

+----------+-------+
| name     | title |
+----------+-------+
| French   | NULL  |
| German   | NULL  |
| Italian  | NULL  |
| Japanese | NULL  |
| Mandarin | NULL  |
+----------+-------+
5 rows in set (0.00 sec)

f.film_id is the primary key of film, so a matched row always has one. A NULL there can only have come from the join filling in a row that matched nothing. Test any NOT NULL column of the right table and the same reasoning holds. The primary key is the safest choice, because it is NOT NULL on every table.

3) The question this answers in practice

Not every film in the catalog is on a shelf. inventory holds the copies, so a film with no inventory row is one nobody can rent.

SELECT f.title
FROM film AS f
LEFT JOIN inventory AS i ON f.film_id = i.film_id
WHERE i.film_id IS NULL
ORDER BY f.title;

+------------------------+
| title                  |
+------------------------+
| ALICE FANTASIA         |
| APOLLO TEEN            |
| ARGONAUTS TOWN         |
...
+------------------------+
42 rows in set (0.00 sec)

42 of the 1,000 films have no copies. That pattern, a LEFT JOIN plus WHERE <right key> IS NULL, is how you find rows in one table with nothing matching in another.

A filter in WHERE undoes the outer join

This is the mistake that costs people the most time with LEFT JOIN. Adding an ordinary condition on the right table looks harmless.

SELECT l.name, f.title
FROM language AS l
LEFT JOIN film AS f ON l.language_id = f.language_id
WHERE f.title LIKE 'ACADEMY%'
ORDER BY l.name, f.title;

+---------+------------------+
| name    | title            |
+---------+------------------+
| English | ACADEMY DINOSAUR |
+---------+------------------+
1 row in set (0.00 sec)

1 row. The 5 unmatched languages are gone, so the query is an inner join now whatever it says. The reason is order: the join runs first and produces the 5 rows with NULL in f.title, then WHERE tests each row, and NULL LIKE 'ACADEMY%' is NULL rather than true, so WHERE drops them. Move the same condition into ON and it becomes part of what counts as a match.

SELECT l.name, f.title
FROM language AS l
LEFT JOIN film AS f ON l.language_id = f.language_id AND f.title LIKE 'ACADEMY%'
ORDER BY l.name, f.title;

+----------+------------------+
| name     | title            |
+----------+------------------+
| English  | ACADEMY DINOSAUR |
| French   | NULL             |
| German   | NULL             |
| Italian  | NULL             |
| Japanese | NULL             |
| Mandarin | NULL             |
+----------+------------------+
6 rows in set (0.00 sec)

All 6 languages, and only 1 of them found a matching film. The rule is worth memorizing: on a LEFT JOIN, a condition on the right table belongs in ON, and a condition on the left table belongs in WHERE. The one deliberate exception is IS NULL in example 2, where dropping the matched rows is the whole point.

Summary

Use LEFT JOIN to keep every row of the left table, matched or not.
Expect NULL in every right-hand column of a row that matched nothing.
Test a NOT NULL column of the right table with IS NULL to list only the unmatched rows.
Put a condition on the right table in ON, because in WHERE it drops the unmatched rows.
Read LEFT OUTER JOIN as another spelling of the same clause.

Getting Started

Server Development

Using Extensions

Creating Extensions

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

How LEFT JOIN works

Examples

1) Keep the unmatched rows

2) List only the rows that matched nothing

3) The question this answers in practice

A filter in WHERE undoes the outer join

Summary

See also

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

​How LEFT JOIN works

​Examples

​1) Keep the unmatched rows

​2) List only the rows that matched nothing

​3) The question this answers in practice

​A filter in WHERE undoes the outer join

​Summary

​See also

How LEFT JOIN works

Examples

1) Keep the unmatched rows

2) List only the rows that matched nothing

3) The question this answers in practice

A filter in WHERE undoes the outer join

Summary

See also