Q12 of 26 · SQL
What is the difference between EXISTS and IN in SQL, and when does it matter for QA queries?
Short answer
Short answer: IN evaluates a full list of values; EXISTS short-circuits as soon as one match is found and is safer when the subquery result might contain NULLs. EXISTS is often faster on large subquery result sets.
Detail
IN collects all values from the subquery first, then compares each outer row against that list:
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM users WHERE country = 'AU');
EXISTS runs the subquery once per outer row and stops as soon as it finds one match — it never materialises the full list:
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM users u
WHERE u.id = o.customer_id AND u.country = 'AU'
);
The critical NULL difference: If the IN subquery returns any NULL, the entire NOT IN clause returns no rows (because SQL can't determine that a value is "not equal to unknown"). EXISTS does not have this problem:
-- Safe: users with no orders, even if customer_id can be NULL
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = u.id);
-- Dangerous if customer_id can be NULL:
SELECT * FROM users
WHERE id NOT IN (SELECT customer_id FROM orders); -- may return 0 rows!
In QA work, prefer NOT EXISTS for "find records with no related row" queries. IN is fine for small, known-clean value lists (e.g., IN ('active', 'pending')).