LEFT JOIN: keep every row
List every member or book, with their loans where they have any.
LEFT JOIN keeps every row of the first table, matched or not.
Show every book's title and the loan_date of each time it was borrowed. Books nobody has borrowed must be in the list too.
Run your query to see the result table.
Press the green Run button or Ctrl+Enter.
How LEFT JOIN works
LEFT JOIN keeps every row of the first table (after FROM), matched or not.
members
| member_id | name |
|---|---|
| 101 | Ava |
| 102 | Ben |
| 103 | Cara |
| 104 | Dev |
loans
| loan_id | member_id | loan_date |
|---|---|---|
| 1 | 101 | 2026-01-12 |
| 2 | 103 | 2026-02-03 |
| 3 | 101 | 2026-03-20 |
| 4 | 102 | 2026-04-08 |
| 5 | 105(deleted) | 2026-05-14 |
query
SELECT members.name, loans.loan_date FROM members LEFT JOIN loans ON members.member_id = loans.member_id;
result: 5 rows
| name | loan_date |
|---|---|
| Ava | 2026-01-12 |
| Ava | 2026-03-20 |
| Ben | 2026-04-08 |
| Cara | 2026-02-03 |
| Dev | NULL |
Keeps every member, matched or not. Dev has no loans, so loan_date is NULL.
What does
LEFT JOINdo?FROM members LEFT JOIN loans ON members.member_id = loans.member_idIt keeps every row of the first table, whether or not it has a match. Where there's no match, the second table's columns are
NULL.Which table is "left"?
The one written first, after
FROM. Swap them and you keep every loan instead of every member.What happens to a member with three loans?
They appear three times, once per loan. A member with no loans appears once, with NULLs.
What about
RIGHT JOIN?It keeps every row of the second table instead. Most people write
LEFT JOINand put the table they want to keep first.How is this like Excel?
It's a lookup that keeps every row of your list, showing #N/A where nothing was found, like Lookup & Merge's "keep all".
How other databases do it
The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.
| Database | How | Note |
|---|---|---|
| SQLite (these lessons) | LEFT JOIN ... ON | The only way. |
| PostgreSQL | LEFT JOIN ... ON | The only way. |
| MySQL / MariaDB | LEFT JOIN ... ON | The only way. |
| SQL Server | LEFT JOIN ... ON | Old code may have WHERE a.id *= b.id; it no longer runs. |
| Oracle | LEFT JOIN ... ON | Old code writes WHERE m.member_id = l.member_id(+): the (+) goes on the side that may be empty. |
| Sybase ASE | LEFT JOIN ... ON | Old code writes WHERE m.member_id *= l.member_id, which still runs. |
| Database | How | Note |
|---|---|---|
| SQLite (these lessons) | Both | Since SQLite 3.39 (2022). |
| PostgreSQL | Both | |
| MySQL / MariaDB | RIGHT only | No FULL JOIN: combine a LEFT and a RIGHT JOIN with UNION. |
| SQL Server | Both | |
| Oracle | Both | |
| Sybase ASE | RIGHT only | No FULL JOIN: combine a LEFT and a RIGHT JOIN with UNION. |
Know someone fighting with a spreadsheet? Send them this page.