LEFT JOIN

Finding what's missing

Module 5 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Find the books nobody borrowed, or members who never borrowed.

LEFT JOIN, then keep the rows where the other table came back empty: IS NULL.

Next: Start with LEFT JOIN

On your own New data, same idea

Show the name of every member who has never borrowed a book, so the library can send them a welcome offer.

The database

5 rows

The database

9 rows

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How LEFT JOIN, IS NULL works

LEFT only keeps rows of the first table with no match.

members

loans

query

SELECT members.name
FROM members LEFT JOIN loans
  ON members.member_id = loans.member_id
WHERE loans.loan_id IS NULL;

result: 1 row

name
Dev

Keeps only members with no match: Dev has never borrowed.

How do I find rows with no match?

FROM books LEFT JOIN loans ON books.book_id = loans.book_id WHERE loans.loan_id IS NULL
LEFT JOIN keeps every book; the books with no loans have NULLs in the loans columns; IS NULL keeps only those.
Which column should IS NULL test?

One that is never empty in the right-hand table, such as its id. A column that can be NULL anyway (like returned) would also catch rows that did match.
Is there another way?

WHERE book_id NOT IN (SELECT book_id FROM loans) reads more like English. Careful: if the inner list contains a NULL, NOT IN returns nothing at all. NOT EXISTS doesn't have that trap.
Why is this useful?

It answers "what's missing?": stock that never sells, customers who never ordered, invoices with no payment.
How is this like Excel?

Like =ISNA(MATCH(A2, Loans!B:B, 0)) filled down and filtered to TRUE, or Lookup & Merge's "Show what's missing".

Other databases

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Rows in one list but not another, with a set operator
Database	How	Note
SQLite (these lessons)	`SELECT book_id FROM books EXCEPT SELECT book_id FROM loans`
PostgreSQL	`EXCEPT`
MySQL / MariaDB	`EXCEPT`	Since MySQL 8.0.31. Before that, use LEFT JOIN or NOT EXISTS.
SQL Server	`EXCEPT`
Oracle	`MINUS`	EXCEPT also works since Oracle 21c.
Sybase ASE	`Not available`	Use LEFT JOIN ... IS NULL, or NOT EXISTS.

Know someone fighting with a spreadsheet? Send them this page.