Module 5: Subqueries and CTEs · Lesson 5.2 · about 25 min
IN and EXISTS with subqueries
Filter one table by a list that another query makes: customers who bought tech, orders placed on Black Friday. Learn IN and EXISTS, why they beat a join for "has at least one", and when NOT IN goes wrong.
Module 5 · about 25 minutes · read first: 5.1 and 4.4. You’ll be able to filter rows with IN and EXISTS subqueries, pick between them, and avoid duplicates and the NOT IN NULL trap.
In everyday terms: a bouncer with a guest list. Your name is either on the list or not; being on it three times does not let you in three times.
1. Why this matters
“Show me customers who bought electronics”, “orders placed during a sale”, “stores that sold anything in Boxing Week”: the list you filter by comes from another table. IN and EXISTS let you say “keep rows whose value is on that list” in one readable line, without copying rows the way a join does. You will use them constantly in ad-hoc analysis.
2. IN with a list you type
You met IN in Lesson 2.3 with a typed list:
-- try
SELECT order_id, item, amount
FROM mini_orders
WHERE customer_id IN (1, 3)
IN (1, 3) is short for customer_id = 1 OR customer_id = 3.
3. IN with a subquery
Instead of typing the list, let a query make it. The subquery must return one column; it can return any number of rows.
Question: which mini customers bought a kettle?
-- try
SELECT id, name
FROM mini_customers
WHERE id IN (SELECT customer_id FROM mini_orders WHERE item = 'Kettle')
The inner query returns the list (1, 3); the outer query keeps Asha and Chen. Press Show how it runs to watch it.
On Northbay, the list often comes from a lookup table. Question: how many orders were placed during a Black Friday sale (there were two, in 2024 and 2025)?
-- try
SELECT COUNT(*) AS black_friday_orders
FROM orders
WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name = 'Black Friday')
135 orders. If next year’s Black Friday is added to promotions, the query picks it up without changes.
4. IN vs a join: no copies
Question: how many customers have bought something from the Tech department? Tech is three steps away: order lines → products → categories.
-- try
SELECT COUNT(*) AS tech_customers
FROM customers
WHERE customer_id IN (
SELECT o.customer_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories c ON c.category_id = p.category_id
WHERE c.department = 'Tech'
)
255 customers. A plain join from customers to those lines gives 345 rows, because a customer who bought tech three times appears three times; that is fan-out (Lesson 4.5). IN only asks “is this customer on the list?”, so each customer counts once.
5. EXISTS: the same question, row by row
EXISTS asks whether a subquery returns any row at all. The subquery refers to the outer row, so it is checked for each customer:
-- try
SELECT COUNT(*) AS tech_customers
FROM customers cu
WHERE EXISTS (
SELECT 1
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories c ON c.category_id = p.category_id
WHERE c.department = 'Tech'
AND o.customer_id = cu.customer_id
)
Also 255. So which should you use?
- IN reads most naturally when the subquery makes a simple list on its own (“promo ids called Black Friday”).
- EXISTS suits conditions that tie the inner rows to the outer row, especially on more than one column (same customer and same store).
- For NOT, always prefer
NOT EXISTS.NOT INreturns nothing if the list contains a NULL, as you saw in Lesson 4.4.orders.customer_idhas 482 NULLs, soNOT IN (SELECT customer_id FROM orders)is a trap.
Modern databases usually run IN and EXISTS the same way, so choose the one that reads best.
Maths and principle
Principle: IN tests set membership; EXISTS tests whether a set is non-empty. Both are semi-joins: they keep a row of the outer table at most once.
Equation: x IN S ⟺ x ∈ S, where S = π_col(subquery). EXISTS(S) ⟺ |S| > 0. In relational algebra both give Customers ⋉ TechLines, while a join gives Customers ⋈ TechLines, whose size is Σ (matches per customer). Here that is 345 versus 255.
Analogy: IN is checking a name against a printed guest list. EXISTS is asking the doorman, “has anyone with this name come through tonight?” Either way, you let the person in once, however many times their name appears.
Try it: SELECT COUNT(*) FROM stores WHERE store_id IN (SELECT store_id FROM orders WHERE promo_id = 7). Did every store sell something in Boxing Week 2025? (Yes: all 12.)
- IN (subquery)
- Keeps the row if its value is one of the values the subquery returns. The subquery returns one column.
- EXISTS (subquery)
- Keeps the row if the subquery returns at least one row. The subquery usually mentions the outer row.
- Semi-join
- The name for this kind of filter: rows of one table that have a partner in another, each kept once.
- NOT IN with NULL
- If the list holds a NULL, NOT IN is never true, so it returns no rows. Use NOT EXISTS.
- Physical meaning
- No columns are added and no rows are copied: the outer table only gets shorter. A join, by contrast, makes one row per matching pair.
“NOT IN and NOT EXISTS are the same.” They differ as soon as the list contains a NULL: id NOT IN (1, NULL) means id <> 1 AND id <> NULL, and a comparison with NULL is unknown, so no row passes. NOT EXISTS ignores NULLs and gives the answer you meant.
Watch it happen, one step at a time (any live box’s Show how it runs button animates the real query on Northbay):
| order_id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 1 |
| 103 | 2 |
| 104 | 3 |
| 105 | 3 |
| 106 | 9 |
SELECT customer_id FROM mini_orders gives 1, 1, 2, 3, 3, 9. As a set: 1, 2, 3, 9.| id | name | on list? |
|---|---|---|
| 1 | Asha | yes |
| 2 | Ben | yes |
| 3 | Chen | yes |
| 4 | Dina | no |
| 5 | Eli | no |
NOT IN returns 0 rows instead of Dina and Eli.6. Worked example: tech buyers by loyalty tier
Question: among customers who bought tech, how are loyalty tiers split?
-- try
SELECT COALESCE(loyalty_tier, '(none)') AS tier, COUNT(*) AS customers
FROM customers
WHERE customer_id IN (
SELECT o.customer_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories c ON c.category_id = p.category_id
WHERE c.department = 'Tech'
)
GROUP BY tier
ORDER BY customers DESC
Bronze 161, Silver 56, no tier 21, Gold 17: 255 in all. Because IN never copies a customer, the tier counts add up to the customer count, which is the check that tells you no fan-out crept in.
7. Practice
-- exercise: List the names of products that were returned at least once with the reason 'Damaged'.
-- hint: WHERE product_id IN (SELECT product_id FROM returns WHERE reason = 'Damaged').
SELECT product_name
FROM products
-- answer:
SELECT product_name
FROM products
WHERE product_id IN (SELECT product_id FROM returns WHERE reason = 'Damaged')
-- exercise: Count the orders placed at stores in the Prairies region. Use IN with a subquery on stores and regions, not a join in the outer query.
-- hint: WHERE store_id IN (SELECT s.store_id FROM stores s JOIN regions r ON r.region_id = s.region_id WHERE r.region_name = 'Prairies').
SELECT COUNT(*) AS orders
FROM orders
-- answer:
SELECT COUNT(*) AS orders
FROM orders
WHERE store_id IN (
SELECT s.store_id
FROM stores s
JOIN regions r ON r.region_id = s.region_id
WHERE r.region_name = 'Prairies'
)
-- exercise: List the first_name and last_name of Gold customers who have placed at least one online order. Use EXISTS.
-- hint: WHERE loyalty_tier = 'Gold' AND EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.channel = 'online').
SELECT c.first_name, c.last_name
FROM customers c
WHERE c.loyalty_tier = 'Gold'
-- answer:
SELECT c.first_name, c.last_name
FROM customers c
WHERE c.loyalty_tier = 'Gold'
AND EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.channel = 'online')
- IN (subquery) keeps rows whose value is on a list made by a query.
- EXISTS keeps rows for which the subquery finds at least one row.
- Both keep each outer row once: no fan-out, unlike a join.
- For “not on the list”, use NOT EXISTS: NOT IN returns nothing if the list has a NULL.
New words: IN subquery, membership, EXISTS subquery.
Check yourself
Sources
- SQLite documentation, The IN and NOT IN operators; EXISTS https://www.sqlite.org/lang_expr.html#in_op
- 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.