IN and EXISTS are both used to check for matching values, typically against a subquery, but they differ in evaluation strategy -- IN compares a value against a full list or subquery result, while EXISTS simply checks whether a subquery returns any rows at all.
Key Points: • IN is straightforward and works well for small, static lists or small subquery result sets. • EXISTS stops as soon as it finds one matching row, which often makes it more efficient than IN on large subqueries. • EXISTS is generally safer with NULLs, since an IN list containing a NULL can produce unexpected results with NOT IN. • EXISTS is commonly used with correlated subqueries, where the inner query references a column from the outer query.
Example: To find employees who are also managers, you could check WHERE id IN (SELECT id FROM managers), or equivalently use WHERE EXISTS (SELECT 1 FROM managers WHERE employees.id = managers.id) -- the EXISTS version typically performs better as the managers table grows large.
Code Example:
-- Using IN
SELECT * FROM employees WHERE id IN (SELECT id FROM managers);
-- Using EXISTS
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM managers m WHERE e.id = m.id);Interview Tip: A concise interview answer is:
"IN checks a value against a list or subquery result, while EXISTS checks whether a correlated subquery returns any rows at all and can short-circuit as soon as it finds a match. I lean toward EXISTS for larger or correlated subqueries since it tends to perform better and avoids NULL pitfalls that NOT IN can run into."