What is EXISTS vs IN?
SQLLevel: IntermediateSubqueries & CTEs
What is EXISTS vs IN?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
IN checks if a value matches any value in a list or subquery result. EXISTS checks whether a subquery returns any rows at all.
-- IN: materializes the subquery result, then checks membership
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US');
-- EXISTS: stops as soon as it finds the first matching row
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.country = 'US');
EXISTS is often faster because it short-circuits. IN can be slow if the subquery returns many rows. Also: IN with NULLs in the subquery can produce surprising results — if the subquery returns any NULL, NOT IN returns no rows at all.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT