IN vs EXISTS
Overview
When checking if a value exists within a list or a subquery, developers debate between IN and EXISTS. IN compares a value against a massive list (WHERE id IN (1,2,3)). EXISTS is a boolean operator; it doesn't care about the actual data, it just checks if the subquery returns at least one row. Knowing when to use which is a hallmark of a senior backend engineer.
Syntax
-- 1. Using IN (Great for small, static lists)
SELECT name FROM products
WHERE category_id IN (1, 4, 7);
-- 2. Using IN with a subquery (Can be slow on massive datasets)
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
-- 3. Using EXISTS (Highly optimized for massive tables!)
SELECT name FROM users u
WHERE EXISTS (
-- EXISTS stops executing the exact microsecond it finds the first match!
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.amount > 100
);Common Pitfalls
- Using
NOT INwith a subquery that contains aNULLvalue. This is one of the deadliest traps in SQL. If you writeWHERE id NOT IN (SELECT id FROM deleted_users), and thedeleted_userstable contains even ONE singleNULLid, the entire query will instantly return ZERO rows.NULLcorruptsNOT IN. Always useNOT EXISTSinstead. - Selecting actual data inside
EXISTS. WritingEXISTS (SELECT * FROM...)is bad practice.EXISTSonly checks for the presence of a row. It is an industry standard to writeEXISTS (SELECT 1 FROM...)to prove to the optimizer you aren't trying to fetch data.
Interview Questions
EXISTS usually faster than IN when dealing with millions of relational rows?IN evaluates the entire inner query, builds a massive list in memory, and then scans it. EXISTS utilizes 'Short-Circuit Evaluation'. Because it only cares if a row exists, the exact millisecond it finds a single matching row, it returns TRUE and stops searching entirely.
Real-World Example
Finding all VIP Users (users who have placed at least one massive order).
SELECT u.id, u.email
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
AND o.total_amount > 5000
);Check Your Knowledge
Test your understanding of IN vs EXISTS with these quick questions.