Finding missing data with LEFT JOIN ... IS NULL
35 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Marketing asks: "Which customers have never placed an order?" The answer is rows that are missing from another table, and an ordinary join cannot show something that is not there.
The anti-join pattern
An anti-join returns the rows of one table that have no match in another table. In MySQL it is written as a LEFT JOIN followed by WHERE right_key IS NULL.
Which events nobody registered for? Join every event to its registrations, then keep the rows where the registration is missing.
Example 1 · Events with no registrations
DROP TABLE IF EXISTS fest_registrations, fest_events;CREATE TABLE fest_events (event_id INT PRIMARY KEY, event VARCHAR(30));CREATE TABLE fest_registrations ( reg_id INT PRIMARY KEY, event_id INT, student VARCHAR(30));INSERT INTO fest_events VALUES (1, 'Coding'), (2, 'Quiz'), (3, 'Debate'), (4, 'Dance'), (5, 'Robotics');INSERT INTO fest_registrations VALUES (101, 1, 'Nisha'), (102, 1, 'Omar'), (103, 2, 'Pallavi'), (104, 4, 'Qadir'), (105, 4, 'Riya');
-- Step 1: the left join keeps every event, NULLs where nobody registeredSELECT e.event, r.reg_id, r.studentFROM fest_events eLEFT JOIN fest_registrations r ON r.event_id = e.event_idORDER BY e.event_id, r.reg_id;
-- Step 2: keep only the NULL rowsSELECT e.eventFROM fest_events eLEFT JOIN fest_registrations r ON r.event_id = e.event_idWHERE r.reg_id IS NULLORDER BY e.event_id;+----------+--------+---------+ | event | reg_id | student | +----------+--------+---------+ | Coding | 101 | Nisha | | Coding | 102 | Omar | | Quiz | 103 | Pallavi | | Debate | NULL | NULL | | Dance | 104 | Qadir | | Dance | 105 | Riya | | Robotics | NULL | NULL | +----------+--------+---------+ +----------+ | event | +----------+ | Debate | | Robotics | +----------+
What this code does
- Step 1 shows the idea: Debate and Robotics are kept by the left join with
NULLin every registration column. - Step 2 adds
WHERE r.reg_id IS NULL, which keeps exactly those rows.reg_idis the registrations table's primary key, so it isNULLonly when there was no match.
The anti-join pattern is LEFT JOIN the other table, then keep only the rows where the other table's key IS NULL. The left join keeps every row and fills unmatched ones with NULL; the WHERE then keeps exactly those unmatched rows.
Why it exists: "which rows are missing?" cannot be answered by looking for rows, because the missing ones are not there. A fest organiser asking "which events got zero registrations?" needs the events table to supply the names and the registrations table to prove the absence. Write the test as IS NULL, never = NULL. x = NULL is never true, so WHERE r.reg_id = NULL returns nothing at all, with no error.
Customers who never ordered
customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL lists customers with no orders: the classic interview query. A shop uses it to send a first-order coupon.
Example 2 · Customers who never ordered
SELECT c.customer_id, c.name, c.city, c.joined_onFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE o.order_id IS NULLORDER BY c.customer_id;+-------------+------------+-----------+------------+ | customer_id | name | city | joined_on | +-------------+------------+-----------+------------+ | 10 | Imran Khan | Hyderabad | 2025-04-18 | +-------------+------------+-----------+------------+
What this code does
- Every other customer has at least one order, so their rows have a real
o.order_idand fail theIS NULLtest. - Imran Khan joined in April 2025 and has no order, so the left join gave him a
NULLrow: a natural target for a first-order coupon.
Example 3 · Customers with no delivered order
SELECT c.customer_id, c.nameFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'DELIVERED'WHERE o.order_id IS NULLORDER BY c.customer_id;+-------------+-------------+ | customer_id | name | +-------------+-------------+ | 3 | Meena Iyer | | 8 | Rahul Verma | | 9 | Sneha Patil | | 10 | Imran Khan | +-------------+-------------+
What this code does
- The status test is in
ON, so a customer "matches" only through a delivered order. Meena (cancelled), Rahul and Sneha (placed) and Imran (no orders) find no match. - If
o.status = 'DELIVERED'were inWHERE, it would demand a delivered order and aNULLorder id on the same row, which is impossible: the query would return no rows.
Example 4 · Trap: testing a column that can be NULL anyway
-- Goal: products whose category row does not exist
-- Wrong: parent_id is also NULL for real, top-level categoriesSELECT p.name, c.name AS categoryFROM products pLEFT JOIN categories c ON c.category_id = p.category_idWHERE c.parent_id IS NULLORDER BY p.product_id;
-- Right: test the primary key of the right tableSELECT p.nameFROM products pLEFT JOIN categories c ON c.category_id = p.category_idWHERE c.category_id IS NULLORDER BY p.product_id;+-------------------+----------+ | name | category | +-------------------+----------+ | Clean Code | Books | | Hands-On Python | Books | | Atomic Habits | Books | | Cotton Kurta | Fashion | | Running Shoes | Fashion | | Rubik's Cube | Toys | | Gift Card Rs. 500 | NULL | +-------------------+----------+ +-------------------+ | name | +-------------------+ | Gift Card Rs. 500 | +-------------------+
What this code does
- The first query also returns books, fashion and toys: their categories exist, but they are top-level, so
parent_idisNULLin a real match. - The second query tests
c.category_id, the primary key, which isNULLonly when no category row matched. Only the gift card is left.
Test a column that cannot be NULL in a real match, ideally the right table's primary key. If you test a column that is sometimes NULL in real rows (a phone number, a parent category), matched rows with that NULL sneak into the answer. Conditions about the right table, such as a status, still belong in ON, as the second example shows.
Predict the output
Predict the anti-join
Only customer 10, Imran Khan, has never placed an order.
Trains with no bookings
An anti-join with extra conditions in ON answers "missing for this case" questions, such as trains with no bookings on 20 October.
Example 5 · Trains with no live booking on a date
DROP TABLE IF EXISTS train_bookings, trains;CREATE TABLE trains (train_no INT PRIMARY KEY, name VARCHAR(40));CREATE TABLE train_bookings ( pnr INT PRIMARY KEY, train_no INT, journey_date DATE, status VARCHAR(10));INSERT INTO trains VALUES (12627, 'Karnataka Express'), (12951, 'Mumbai Rajdhani'), (22691, 'Vande Bharat'), (12839, 'Howrah Mail'), (16526, 'Island Express');INSERT INTO train_bookings VALUES (4501, 12627, '2025-10-20', 'CONFIRMED'), (4502, 12627, '2025-10-21', 'CONFIRMED'), (4503, 12951, '2025-10-20', 'WAITLIST'), (4504, 22691, '2025-10-20', 'CANCELLED'), (4505, 16526, '2025-10-21', 'CONFIRMED');
SELECT t.train_no, t.nameFROM trains tLEFT JOIN train_bookings b ON b.train_no = t.train_no AND b.journey_date = '2025-10-20' AND b.status <> 'CANCELLED'WHERE b.pnr IS NULLORDER BY t.train_no;+----------+----------------+ | train_no | name | +----------+----------------+ | 12839 | Howrah Mail | | 16526 | Island Express | | 22691 | Vande Bharat | +----------+----------------+
What this code does
- Karnataka Express and Mumbai Rajdhani have a live booking on the 20th (a waiting-list ticket counts), so they match and are filtered out.
- Vande Bharat's only booking is cancelled, Island Express is booked on the 21st, and Howrah Mail has no bookings at all: none of them can match, so all three are listed.
Every condition that describes the bookings you are looking for, the date and "not cancelled", goes in ON; only the IS NULL test goes in WHERE.
Read it as: "for each train, try to find a live booking for that date; keep the trains where none was found". A train with only a cancelled booking, or with bookings only on other days, correctly counts as having none. Railway, cinema and hostel systems use this shape all the time: empty seats, unbooked shows, unallotted rooms. The project at the end of this module uses it on a full railway database.
Find the bug
Find the wrong line
Each query should list customers without a matching order. Click the wrong line.
Customers with no DELIVERED order.
Round 1 of 2Comparing with NOT EXISTS
NOT EXISTS (subquery) is true when the subquery returns no row. It is the second way to write an anti-join: keep a row only if nothing in the other table matches it.
NOT EXISTS (subquery) is the other common anti-join form. A subquery is a SELECT written inside another statement; NOT EXISTS is true when it returns no rows.
Example 6 · NOT EXISTS, and the NOT IN trap
-- NOT EXISTS: departments for which no employee row existsSELECT d.dept_nameFROM departments dWHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id)ORDER BY d.dept_name;
-- NOT IN: employees.dept_id contains a NULL (Tanvi), so nothing comes backSELECT COUNT(*) AS rows_foundFROM departments dWHERE d.dept_id NOT IN (SELECT dept_id FROM employees);
-- NOT IN with the NULLs removed works againSELECT d.dept_nameFROM departments dWHERE d.dept_id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL)ORDER BY d.dept_name;+-----------+ | dept_name | +-----------+ | Research | +-----------+ +------------+ | rows_found | +------------+ | 0 | +------------+ +-----------+ | dept_name | +-----------+ | Research | +-----------+
What this code does
NOT EXISTSfinds Research, exactly likeLEFT JOIN employees ... WHERE e.emp_id IS NULLwould.5 NOT IN (1, 2, 3, 4, NULL)is not true but unknown, because 5 might equal the unknown value. So the plainNOT INfinds 0 rows; removing theNULLs from the list fixes it.
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id) keeps departments for which no employee row exists. The Subqueries module covers subqueries in depth.
Both forms give the same answer, and MySQL 8.0.17 and later usually runs them with the same plan, so choose the one your team reads more easily. NOT EXISTS states the intent directly; LEFT JOIN ... IS NULL works in every version and is easy to extend into a report. Avoid NOT IN (subquery) when the subquery's column can be NULL. If the list contains a single NULL, x NOT IN (...) is never true, and the query silently returns no rows. Either add WHERE col IS NOT NULL inside the subquery or use NOT EXISTS.
NOT IN returns nothing when the list contains a NULL; NOT EXISTS is not affected.Next step
Check your understanding
12 quick questions