Module 4: Joins · Lesson 4.4 · about 25 min
Anti-joins and semi-joins: NOT EXISTS and EXISTS
Find rows that have no match (anti-join) or at least one match (semi-join) without copying rows. Compare NOT EXISTS, LEFT JOIN ... IS NULL and NOT IN, and see the NULL trap that makes NOT IN return nothing.
Module 4 · about 25 minutes · read first: 4.3. You’ll be able to find rows with no match and rows with at least one match, choose between NOT EXISTS, LEFT JOIN and NOT IN, and avoid the NOT IN NULL trap.
In everyday terms: a teacher with a register and a pile of homework. “Who handed nothing in?” is an anti-join; “who handed something in?” is a semi-join. The teacher doesn’t care whether a student handed in one sheet or three, only whether any sheet exists.
1. Why this matters
“Customers who never bought”, “products never returned”, “stores that sold nothing last week”: these questions come up every week in analyst work. They are not really joins, because you don’t want any columns from the other table; you only want to know whether a match exists. SQL has a clean way to ask exactly that, and one popular way that quietly breaks.
2. Two questions, two names
- An anti-join keeps rows from table A that have no match in table B.
- A semi-join keeps rows from table A that have at least one match in table B, each row once.
Neither adds columns from B, and neither repeats rows. That is the difference from an inner join, which repeats a customer once per order.
3. NOT EXISTS: the anti-join
EXISTS (subquery) is true when the subquery returns at least one row. NOT EXISTS is true when it returns none. Inside the subquery you refer to the outer row (c.id), so it is checked once for each customer:
-- try
SELECT c.id, c.name
FROM mini_customers c
WHERE NOT EXISTS (
SELECT 1 FROM mini_orders o WHERE o.customer_id = c.id
)
Dina and Eli, the customers with no orders. The SELECT 1 doesn’t matter; EXISTS only cares whether any row comes back, so people write 1 or *.
On Northbay, the same pattern finds products that were never returned:
-- try
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM returns r WHERE r.product_id = p.product_id
)
Four products out of 61: strawberries, sourdough, eggs and salted cashews. All cheap groceries, which makes sense.
4. EXISTS: the semi-join
Swap to EXISTS and you get the customers who do have a match. Compare it with an inner join:
-- try
SELECT c.id, c.name
FROM mini_customers c
WHERE EXISTS (
SELECT 1 FROM mini_orders o WHERE o.customer_id = c.id
)
Asha, Ben and Chen, each once. An inner join would show Asha and Chen twice (two orders each), and you would need DISTINCT to undo it.
5. Three ways to write an anti-join
You met the first one in Lesson 4.3. All three ask “which customers have never ordered?”:
-- try
-- 1. LEFT JOIN ... IS NULL
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
-- try
-- 2. NOT EXISTS
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
Both return customers 601, 602 and 603. Now the third:
-- try
-- 3. NOT IN
SELECT c.customer_id
FROM customers c
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders)
No rows at all. This is the NOT IN trap. orders.customer_id is NULL for the 482 guest orders. x NOT IN (list) means “x is different from every item”, and “different from NULL” is never true or false, only unknown. One NULL in the list makes NOT IN fail for every row. No error, just an empty answer that looks believable.
If you must use NOT IN, filter the NULLs out: NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL). Better: make NOT EXISTS your habit. It is NULL-safe, it never copies rows, and every major database runs it efficiently.
Maths and principle
Principle: a semi-join keeps the rows of A that have a partner in B; an anti-join keeps the rest. Together they split A into two parts with no overlap.
Equation: semi-join A ⋉ B = π_A(A ⋈ B), and anti-join A ▷ B = A − (A ⋉ B). So |A ⋉ B| + |A ▷ B| = |A|. For mini_customers: 3 + 2 = 5. NOT IN fails because SQL uses three-valued logic: TRUE, FALSE and UNKNOWN. x ≠ NULL is UNKNOWN, TRUE AND UNKNOWN is UNKNOWN, and WHERE keeps only TRUE.
Analogy: a guest list at the door. EXISTS asks “is your name on the list?”; it doesn’t matter if it is written three times. NOT IN asks “is your name different from every name on the list?” If one line is smudged and unreadable, the doorman can never say yes.
Try it: Northbay has 603 customers and 3 never ordered. How many does EXISTS return? (600.)
- Anti-join
- Rows of A with no match in B.
- Semi-join
- Rows of A with at least one match in B, each shown once.
- EXISTS (subquery)
- True if the subquery returns at least one row.
- NOT EXISTS (subquery)
- True if it returns none. NULL-safe.
- NOT IN trap
- One NULL in the NOT IN list makes the test unknown for every row, so nothing comes back.
- Physical meaning
- Neither adds columns or copies rows: each row of A is checked once and either kept or dropped. Semi-join plus anti-join always gives back exactly the rows of A.
“NOT IN (SELECT …) and NOT EXISTS are the same thing.” They agree only when the list has no NULLs. Northbay’s orders have 482 guest rows with a NULL customer_id, so NOT IN (SELECT customer_id FROM orders) returns nothing at all, without an error. Use NOT EXISTS by habit.
Watch both tests on the same customers, one step at a time (the live boxes’ Show how it runs button animates the real queries):
| id | name |
|---|---|
| 1 | Asha |
| 2 | Ben |
| 4 | Dina |
| orders.customer_id |
|---|
| 1 |
| 1 |
| 2 |
| NULL |
| id | name |
|---|---|
| 1 | Asha |
| 2 | Ben |
| id | name |
|---|---|
| 4 | Dina |
6. Worked example: loyal customers who returned something
Question: how many loyalty members have returned at least one item?
Returns point at orders, and orders point at customers. A semi-join asks for customers where such a pair exists:
-- try
SELECT COUNT(*) AS customers_with_returns
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
JOIN returns r ON r.order_id = o.order_id
WHERE o.customer_id = c.customer_id
)
The answer is 153. With an inner join and COUNT(*) you would count return rows, not customers, and get a bigger, wrong number.
7. Practice
-- exercise: List the customer_id, first_name and last_name of customers who have ordered online but never in a store.
-- hint: Two tests in WHERE: EXISTS an order with channel = 'online', AND NOT EXISTS an order with channel = 'store'.
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.channel = 'online')
-- answer:
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.channel = 'online')
AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.channel = 'store')
-- exercise: List the names of categories where no product was ever returned with the reason 'Not as described'.
-- hint: NOT EXISTS with a subquery that joins returns to products and checks p.category_id = c.category_id and r.reason = 'Not as described'.
SELECT c.category_name
FROM categories c
-- answer:
SELECT c.category_name
FROM categories c
WHERE NOT EXISTS (
SELECT 1
FROM returns r
JOIN products p ON p.product_id = r.product_id
WHERE p.category_id = c.category_id
AND r.reason = 'Not as described'
)
-- exercise: Fix the NOT IN query so it finds the 3 customers who never ordered.
-- hint: Keep NOT IN, but remove the NULLs from the subquery with WHERE customer_id IS NOT NULL. Or rewrite it with NOT EXISTS.
SELECT c.customer_id
FROM customers c
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders)
-- answer:
SELECT c.customer_id
FROM customers c
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL)
- An anti-join keeps rows with no match; a semi-join keeps rows with at least one, each once.
- NOT EXISTS / EXISTS ask only whether a match exists; they never copy rows or add columns.
- LEFT JOIN … WHERE right_key IS NULL is another correct anti-join.
- NOT IN breaks if the list holds a NULL; filter NULLs out or use NOT EXISTS.
- Semi-join count + anti-join count = rows in the first table.
New words: anti-join, semi-join, EXISTS, NOT EXISTS, NOT IN trap, three-valued logic.
Check yourself
Sources
- SQLite documentation, SQL Language Expressions: The EXISTS operator and IN operator https://www.sqlite.org/lang_expr.html
- PostgreSQL documentation, Subquery Expressions (EXISTS, IN, NOT IN) https://www.postgresql.org/docs/current/functions-subquery.html
- Microsoft Learn, EXISTS (Transact-SQL) https://learn.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.