What is the difference between IN and EXISTS?
IN compares a value against a list of values returned by a subquery. EXISTS only checks whether the subquery returns any row and stops at the first match. EXISTS handles NULLs more safely, especially with NOT.
Explain it simply
IN is like checking whether your roll number is on a printed list. EXISTS is like asking the office, "Is there any receipt for this student?" and the clerk stops searching the moment one receipt turns up.
Example
-- customers who have placed at least one order
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);Both return the same rows. Modern optimizers usually plan them identically, so performance differences are small and depend on the database.
The real difference is NULL behaviour with NOT:
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);If any customer_id in orders is NULL, this returns no rows, because id <> NULL is unknown. NOT EXISTS does not have this problem.
EXISTS also works when matching on several columns, which IN cannot do simply.
What to say in the interview
"They give the same result for positive checks. EXISTS short-circuits and is NULL-safe, so I prefer NOT EXISTS over NOT IN. I check the execution plan when performance matters."
Common mistakes
- Using NOT IN on a nullable column.