EXISTS vs IN
Two ways to check for related rows — and when each one wins
Advanced 14 minEXISTSINNOT EXISTS
EXISTS vs IN
Both EXISTS and IN check whether related rows exist in another table — but they work differently under the hood, and the difference matters for both correctness and performance. Here's when to use which:
IN collects, then checks — Gathers all subquery values into a list and checks if the outer value matches any item. Simple and readable for static lists:
WHERE country IN ('India', 'Japan', 'Australia').EXISTS short-circuits — Runs a correlated subquery per outer row and returns true at the first match found. No need to collect all values — it stops immediately, making it faster for large datasets with early matches.
The NOT IN trap —
WHERE id NOT IN (1, 2, NULL) returns zero rows. Comparing anything to NULL yields unknown, and NOT unknown is still unknown. NOT EXISTS doesn't have this problem — it's always NULL-safe.Rule of thumb — Use IN for small, static value lists where readability matters most. Use EXISTS for correlated checks against large tables. Default to NOT EXISTS over NOT IN to avoid the NULL trap entirely.
Knowledge Canvas
EXISTS vs IN
Two approaches to checking related rows
- IN: collect all values, check membership
- EXISTS: check row-by-row, stop at first match
- NOT IN with NULLs = 0 rows (the most famous SQL bug)
- NOT EXISTS is always NULL-safe
The NOT IN + NULL Trap
This bug costs companies millions
- WHERE id NOT IN (1, 2, NULL) → returns ZERO rows
- NULL makes every NOT IN comparison UNKNOWN
- NOT EXISTS doesn't have this problem — always safe
- Always prefer NOT EXISTS over NOT IN
-- BROKEN: returns 0 rows if any customer_id is NULL
WHERE id NOT IN (SELECT customer_id FROM orders)
-- SAFE: always works correctly
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
)Performance Comparison
When speed matters
- EXISTS short-circuits at first match → faster for large sets
- IN materializes the full list → OK for small static lists
- Most optimizers rewrite IN → semi-join (same plan as EXISTS)
- NOT EXISTS typically faster than NOT IN (NULL overhead)
Decision Rules
Quick guide to choosing
- Small literal list → IN:
WHERE status IN ('a','b','c') - Large subquery → EXISTS: correlated + short-circuit
- NOT found check → ALWAYS NOT EXISTS (NULL-safe)
- If in doubt → EXISTS is the safer default
Syntax
Syntax Template
1-- IN — checks membership in a value list
2SELECT *
3FROM customers c
4WHERE c.id IN (
5 SELECT customer_id
6 FROM orders
7);
8
9-- EXISTS — correlated, short-circuits at first match
10SELECT *
11FROM customers c
12WHERE EXISTS (
13 SELECT 1
14 FROM orders o
15 WHERE o.customer_id = c.id
16);
17
18-- NOT EXISTS — NULL-safe "not found" check
19SELECT *
20FROM customers c
21WHERE NOT EXISTS (
22 SELECT 1
23 FROM orders o
24 WHERE o.customer_id = c.id
25);
INChecks membership in a list of valuesEXISTSTrue if correlated subquery returns any rowsNOT EXISTSNULL-safe way to find unmatched rows Sample Data
customers
12 rows
| id | name |
|---|---|
| C1001 | Aarav Sharma |
| C1002 | Sara Chen |
| C1003 | James Wilson |
| C1004 | Maria Garcia |
| C1005 | Yuki Tanaka |
| C1006 | Priya Patel |
| C1007 | Alex Johnson |
| C1008 | Chen Wei |
| C1009 | Emma Brown |
| C1010 | Omar Hassan |
| C1011 | Lena Muller |
| C1012 | Ravi Kumar |
reviews
12 rows
| product_id | customer_id | rating |
|---|---|---|
| P101 | C1001 | 5 |
| P101 | C1003 | 4 |
| P101 | C1005 | 3 |
| P102 | C1002 | 5 |
| P102 | C1006 | 4 |
| P103 | C1004 | 4 |
| P104 | C1007 | 5 |
| P105 | C1009 | 5 |
| P105 | C1012 | 4 |
| P107 | C1008 | 3 |
| P108 | C1011 | 5 |
| P109 | C1001 | 5 |
products
10 rows
| id | name | category | price | stock |
|---|---|---|---|---|
| P101 | Wireless Mouse | Electronics | 24.99 | 150 |
| P102 | Office Desk | Furniture | 189 | 25 |
| P103 | LED Desk Lamp | Furniture | 45.5 | 80 |
| P104 | USB-C Cable | Electronics | 12.99 | 500 |
| P105 | Bluetooth Speaker | Electronics | 79 | 60 |
| P106 | Office Chair | Furniture | 95 | 15 |
| P107 | Coffee Maker | Appliances | 89.99 | 40 |
| P108 | Notebook Set | Stationery | 8.5 | 200 |
| P109 | Wireless Headphones | Electronics | 159 | 30 |
| P110 | Standing Desk | Furniture | 425 | 8 |
Worked Example
Find products that have received at least one 5-star review using EXISTS.
SQL
1SELECT
2 p.name
3FROM products p
4WHERE EXISTS ( SELECT 1 FROM reviews r WHERE r.product_id = p.id AND r.rating = 5 )
5ORDER BY
6 p.name;
For each product, the subquery checks if any review exists with rating = 5 for that product. EXISTS returns true as soon as one such review is found — it does not need to count all of them. The ORDER BY keeps the output deterministic across database runs.
Output
4 rows
| name |
|---|
| Bluetooth Speaker |
| Notebook Set |
| Office Desk |
| USB-C Cable |
Key Concepts
1NOT IN silently breaks if the list contains NULL — returns 0 rows
2NOT EXISTS is always NULL-safe — prefer it over NOT IN
3EXISTS short-circuits at the first match — faster for large sets
4IN is cleaner for small, static value lists
Pro Tip
Choosing between IN and EXISTS affects both correctness (NULL behavior) and performance. NOT IN with NULLs is one of the most common SQL bugs.
When to Use
Checking if customers have orders (EXISTS), filtering by a value list (IN), finding unmatched records (NOT EXISTS).
Find all customers who have NEVER left a review. Use NOT EXISTS. Show the customer name.
Your Query