EXISTS vs IN
- SQL course
- Intermediate
- Lesson 55/115
- 4 min
Learn the difference between EXISTS and IN.
EXISTS vs IN
EXISTS checks existence, IN searches a list. EXISTS is faster on large lists.
-- EXISTS
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Can be more efficient than IN.
name is selected from customers c. WHERE keeps only the rows where EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) holds.
EXISTS vs IN — details
IN loads the full subquery result into memory. EXISTS stops at the first match. It is not affected by NULL issues.
In particular, NOT IN returns no rows if the subquery yields a NULL; NOT EXISTS doesn't fall into that trap.
Practice EXISTS vs IN
Find customers with orders using EXISTS instead of IN.
Starter code
-- with exists
SELECT name FROM customers c
WHERESample solution
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Expected output
name Ayşe Mehmet
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 EXISTS vs IN: match the terms
| Term | Meaning |
|---|---|
EXISTS |
existence |
IN |
list |
fast |
first match |
NULL |
empty |
2 Add EXISTS
SELECT name FROM customers c
WHERE ____ (SELECT 1 FROM orders o WHERE o.customer_id = c.id);SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Explanation: EXISTS stops at the first matching row, which makes it fast on large tables.
3 Which is not affected by NULLs?
- IN
- EXISTS Correct answer
- ANY
- ALL
Explanation: EXISTS is not affected by NULL issues.