Subqueries in WHERE: IN, NOT IN, ANY, ALL

A scalar subquery returns one value, so a simple > or = works. But often the inner query returns a list — for example, the ids of every Orange Cap winner. To compare one value against a whole list, SQL gives you IN, NOT IN, ANY and ALL.

For these examples we use a small extra table:

CREATE TABLE orange_cap (player_id INT, season INT);
INSERT INTO orange_cap VALUES (1, 2024), (3, 2022);   -- Kohli and Buttler

(The playground examples below create it for you in their setup.)

Definition

IN returns TRUE when the value equals at least one value in the subquery's list. NOT IN returns TRUE when the value equals none of the values in the list. ANY (same as SOME) returns TRUE when the comparison is true for at least one value in the list. ALL returns TRUE when the comparison is true for every value in the list.

The subquery for all four must return exactly one column (a list). ANY and ALL are always combined with a comparison operator: > ANY, < ALL, = ANY and so on.

Syntax

SELECT columns
FROM table_a
WHERE column_name  IN        (SELECT one_column FROM table_b);
-- or  NOT IN  (…)
-- or  > ANY   (…)    = ANY is the same as IN
-- or  > ALL   (…)    <> ALL is the same as NOT IN

Example — IN: Orange Cap winners

SELECT id, name, runs
FROM players
WHERE id IN (SELECT player_id FROM orange_cap);

How it works: the inner query returns the list (1, 3). The outer query keeps a player when its id is in that list.

Example — NOT IN: everyone else

SELECT id, name, runs
FROM players
WHERE id NOT IN (SELECT player_id FROM orange_cap);

The NOT IN + NULL trap ⚠️

If the subquery list contains even one NULL, NOT IN returns no rows at all.

Why? id NOT IN (1, 3, NULL) means id <> 1 AND id <> 3 AND id <> NULL. Any comparison with NULL is UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN — so WHERE never sees TRUE. The fix is either filtering NULLs out of the subquery or using NOT EXISTS (next topic):

WHERE id NOT IN (SELECT player_id FROM orange_cap WHERE player_id IS NOT NULL)

Example — ALL: better than every MI player

SELECT id, name, runs
FROM players
WHERE runs > ALL (SELECT runs FROM players WHERE team = 'MI');

MI runs are 500 and 20. > ALL means bigger than both, i.e. bigger than the maximum (500).

Example — ANY: better than at least one MI player

SELECT id, name, runs
FROM players
WHERE runs > ANY (SELECT runs FROM players WHERE team = 'MI');

> ANY means bigger than at least one of (500, 20), i.e. bigger than the minimum (20). Only Bumrah (20) is not bigger than 20.

Note: The playground (SQLite) does not support ANY / ALL. Their exact equivalents with MAX / MIN run everywhere: > ALL (…) ≈ > (SELECT MAX(…)) and > ANY (…) ≈ > (SELECT MIN(…)) (when the list is non-empty and has no NULLs).

Common mistakes

• ❌ WHERE id = (SELECT player_id FROM orange_cap) → the subquery returns 2 rows; SQL Server errors. ✅ WHERE id IN (…).
• ❌ NOT IN over a column that can contain NULL → silently returns 0 rows. ✅ add WHERE col IS NOT NULL inside, or use NOT EXISTS.
• ❌ WHERE id IN (SELECT player_id, season FROM orange_cap) → two columns. ✅ return exactly one column.
• ❌ Thinking > ANY means "bigger than all". ✅ ANY = at least one (minimum); ALL = every one (maximum).
• Edge case: if the subquery returns no rows, > ALL is TRUE for every row and > ANY is FALSE for every row.

Interview tip

Q: Why might NOT IN return no rows when you clearly expect some? Because the subquery returned a NULL. x NOT IN (…, NULL) can never be TRUE, since x <> NULL is UNKNOWN. Use NOT EXISTS or filter out NULLs.