> ## 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 LEFT JOIN
> How a MySQL LEFT JOIN keeps rows that match nothing, how to list those rows with IS NULL, and why a filter in WHERE turns it back into an inner join.
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
```sql theme={null}
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.
```sql theme={null}
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;
```
```text theme={null}
+----------+-----------------------------+
| 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.
```sql theme={null}
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;
```
```text theme={null}
+----------+-------+
| 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.
```sql theme={null}
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;
```
```text theme={null}
+------------------------+
| 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 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.
```sql theme={null}
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;
```
```text theme={null}
+---------+------------------+
| 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.
```sql theme={null}
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;
```
```text theme={null}
+----------+------------------+
| 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.
## See also
* [INNER JOIN](/docs/tutorial/inner-join) — the join that keeps only matches
* [RIGHT JOIN](/docs/tutorial/right-join) — the same idea from the other side
* [IS NULL](/docs/tutorial/is-null) — the test that makes the unmatched rows visible
* [EXISTS](/docs/tutorial/exists) — the same question asked without a join
* [MySQL JOIN performance and common mistakes](/docs/guides/joins) — the same trap, seen as a bug report