✏️ Explanatory Question
Level: Intermediate to Advanced — A frequent question testing subquery performance intuition.
Both IN and EXISTS are used with subqueries to filter rows, but they work differently under the hood:
IN when the subquery returns a small list. Use EXISTS when the subquery is large or correlated, since it short-circuits on the first match.
| Feature | IN | EXISTS |
|---|---|---|
| Checks | Value in a list | Whether rows exist |
| Subquery execution | Builds full result list | Stops at first match |
| Best for | Small subquery results | Large / correlated subqueries |
| NULL handling | Can behave unexpectedly with NULLs | Not affected by NULLs |
| Returns | Matches from the list | Boolean (true/false) |
If the subquery used by IN returns a NULL, a NOT IN comparison can unexpectedly return no rows at all, because comparing anything to NULL yields "unknown". EXISTS / NOT EXISTS does not suffer from this issue.
-- IN: employees in Kolkata-based departments (small list)
SELECT name FROM employees
WHERE dept_id IN (
SELECT dept_id FROM departments WHERE location = 'Kolkata'
);
-- EXISTS: departments that have at least one employee (correlated)
SELECT d.dept_name FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
-- NOT EXISTS: departments with NO employees (safe with NULLs)
SELECT d.dept_name FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
-- NOT IN NULL trap: if the subquery returns a NULL, this may return nothing!
-- SELECT name FROM employees
-- WHERE dept_id NOT IN (SELECT dept_id FROM departments);