IN, ANY and ALL Subqueries
Set-membership subqueries and the famous NOT IN with NULL trap.
IN, ANY and ALL Subqueries is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Set-Membership Subqueries
When a subquery returns a list of values, you test membership against it with IN, ANY, or ALL. These are how SQL asks is this value in that set? or does it beat every / any element of that set?
IN— matches any value in the list.ANY/SOME— true if the comparison holds for at least one element.ALL— true only if it holds for every element.
IN With a Subquery
The everyday case: find employees who work in any department located in 'NYC'. The subquery returns a set of department ids, and IN keeps rows that match any of them.
This reads naturally and is the form interviewers expect first.
SELECT name
FROM employees
WHERE dept_id IN (
SELECT id FROM departments WHERE city = 'NYC'
);= ANY Is the Same as IN
A neat equivalence interviewers like: = ANY (subquery) means exactly the same thing as IN (subquery). Both are true when the value equals at least one element of the set.
The query below returns the identical result to the previous one. ANY and its synonym SOME generalize this to other operators like > and <.
SELECT name
FROM employees
WHERE dept_id = ANY (
SELECT id FROM departments WHERE city = 'NYC'
);ANY With a Comparison Operator
ANY becomes powerful with > or <. salary > ANY (set) is true if salary beats at least the smallest element — i.e. greater than the minimum.
This finds employees who earn more than at least one person in department 5.
SELECT name, salary
FROM employees
WHERE salary > ANY (
SELECT salary FROM employees WHERE dept_id = 5
);ALL With a Comparison Operator
salary > ALL (set) is true only if salary beats every element — i.e. greater than the maximum. This finds employees who out-earn everyone in department 5.
Memorize the shortcut: > ALL = greater than MAX, > ANY = greater than MIN. Interviewers test this constantly.
SELECT name, salary
FROM employees
WHERE salary > ALL (
SELECT salary FROM employees WHERE dept_id = 5
);NOT IN: The Famous NULL Trap
Here is the single most asked subquery interview gotcha. If the subquery in a NOT IN returns even one NULL, the whole NOT IN can yield no rows — not the orphans you expected.
Why? x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. That last comparison is UNKNOWN, so the AND can never be true.
SELECT name
FROM customers
WHERE id NOT IN (
SELECT customer_id FROM orders
);Why That Query Silently Breaks
If orders.customer_id is nullable and any row has a NULL there, the previous query returns zero rows even when customers without orders clearly exist.
- The presence of NULL turns the logic to
UNKNOWN. - It does not error — it just returns wrong (empty) results.
Stating this danger out loud during an interview is a strong signal.
Fixing NOT IN With NULL
Three safe fixes interviewers accept:
- Exclude NULLs in the subquery: add
WHERE customer_id IS NOT NULL. - Rewrite using
NOT EXISTS, which handles NULLs correctly. - Use a
LEFT JOIN ... IS NULLanti-join.
The guarded version below returns the true list of customers with no orders.
SELECT name
FROM customers
WHERE id NOT IN (
SELECT customer_id FROM orders
WHERE customer_id IS NOT NULL
);IN Is Fine With NULLs
Reassuring counterpoint: a plain IN (not negated) is not broken by NULLs in the list. x IN (1, 2, NULL) is true if x equals 1 or 2; the NULL just never matches.
The NULL danger is specific to NOT IN. Knowing the difference between the two cases is exactly what separates a confident answer from a guess.
Multi-Column IN
Some dialects (Postgres, MySQL) allow IN over a tuple of columns, matching pairs at once. This finds order lines whose (product, region) combination appears in a promotions table.
SQL Server lacks row-value IN; you would rewrite it with EXISTS there. Mentioning that portability gap impresses interviewers.
SELECT *
FROM order_lines
WHERE (product_id, region) IN (
SELECT product_id, region FROM promotions
);Interview Soundbite
Say this: "IN tests set membership and equals = ANY. With comparison operators, > ANY means greater than the minimum and > ALL means greater than the maximum. The big trap is NOT IN against a subquery that can return NULL — it silently returns no rows, so I guard with IS NOT NULL or switch to NOT EXISTS."
That answer hits membership, ANY/ALL semantics, and the NULL trap in one breath.
Quick Check
The interviewer's favorite subquery trap.
Recap
Membership subqueries, mastered:
IN== ANY: matches any element of the set.> ANYmeans greater than the minimum;> ALLmeans greater than the maximum.NOT INwith a NULL in the subquery silently returns no rows — guard withIS NOT NULLor useNOT EXISTS.- Plain
INtolerates NULLs; multi-columnINworks in some dialects.
Next: EXISTS vs IN and the performance question senior screens love.
Frequently asked questions
Is the “IN, ANY and ALL Subqueries” lesson free?
Yes — the full text of “IN, ANY and ALL Subqueries” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “IN, ANY and ALL Subqueries”?
Set-membership subqueries and the famous NOT IN with NULL trap. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “IN, ANY and ALL Subqueries” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.