How do IN and NOT IN relate to the quantified predicates = ANY and <> ALL?
answer
- one is existential, the other universal
- think of them as a big OR and a big AND
- IN has an exact quantified equivalent
- SOME spells the same predicate differently
- negating the existential flips the quantifier
basics
~20 sIN is defined as = ANY: TRUE if the value equals at least one subquery row. NOT IN is <> ALL: TRUE only if it differs from every row. SOME is a synonym for ANY.
solid answer
~50 sThe standard defines `x IN (<subquery>)` as exactly `x = ANY (<subquery>)`, and `x NOT IN (<subquery>)` as `x <> ALL (<subquery>)`. `ANY` (spelled `SOME` as a synonym) is existential — TRUE if the comparison holds for at least one returned row — while `ALL` is universal — TRUE only if it holds for every returned row. The trap is assuming symmetry: `x <> ANY (...)` is **not** `NOT IN`. It means "differs from at least one row", which is TRUE for almost any value once the subquery returns two distinct rows. Quantifiers also generalise beyond equality: `x > ALL (SELECT price FROM p)` means "greater than the maximum", and `x > ANY (...)` means "greater than the minimum". These predicates date from SQL-92, but not every engine implements ANY/ALL with subqueries, so check yours before relying on them.
code
sql · 7 lines-- These two predicates are defined to be identical
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'FR')
WHERE customer_id = ANY (SELECT id FROM customers WHERE country = 'FR')
-- And so are these
WHERE customer_id NOT IN (SELECT id FROM customers WHERE country = 'FR')
WHERE customer_id <> ALL (SELECT id FROM customers WHERE country = 'FR')go deeper
Know that IN is a membership test and that = ANY is another spelling of it. You are not expected to reason through > ALL at this level, but recognising the keywords helps.
State the two expansions precisely — IN is = ANY, NOT IN is <> ALL — and explain ANY as a big OR and ALL as a big AND. Be ready to say why <> ANY is not NOT IN.
Use the expansion as your explanation of the NOT IN NULL hazard, and show judgment about readability: IN for membership, > ALL/< ALL for max/min comparisons, <> ANY never.
The angle to own is expressiveness versus legibility across a team: quantified predicates say things IN cannot, but they read ambiguously enough that a written convention beats individual cleverness.
## Quantified comparison predicates SQL has a family of predicates of the shape `<value> <comparison-op> ANY (<subquery>)` and `<value> <comparison-op> ALL (<subquery>)`, where the operator is any of `=`, `<>`, `<`, `<=`, `>`, `>=`. `SOME` is defined as a synonym of `ANY` and means precisely the same thing; it exists so queries can read as English (`x > SOME (...)`). The evaluation rules are: - **ANY / SOME** — TRUE if the comparison is TRUE for at least one returned row; FALSE if it is FALSE for every returned row; UNKNOWN otherwise (no TRUE, but at least one UNKNOWN). - **ALL** — TRUE if the comparison is TRUE for every returned row; FALSE if it is FALSE for at least one row; UNKNOWN otherwise. These mirror `OR` and `AND` over the set of comparisons, which is the easiest way to remember them: ANY is a big OR, ALL is a big AND. ## IN is = ANY `x IN (<subquery>)` is not a separate concept; the standard defines it as `x = ANY (<subquery>)`. Both forms are portable and mean the same thing, and `IN` is preferred simply because it reads better. ```sql SELECT * FROM orders WHERE customer_id = ANY (SELECT id FROM customers WHERE country = 'FR'); -- identical to SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'FR'); ``` ## NOT IN is <> ALL Negating an existential claim gives a universal one: `NOT (x = ANY S)` is `x <> ALL S`. In words, "x is not among them" means "x differs from every one of them". This expansion is worth memorising, because it is the shortest explanation of the NOT IN + NULL trap: a universal claim cannot be TRUE if even one of its conjuncts is UNKNOWN, so a single NULL in the subquery makes the predicate UNKNOWN for every non-matching candidate and the filter keeps nothing. ## The `<> ANY` trap The symmetry people assume does not exist. `x <> ANY (<subquery>)` means "there is at least one returned row that x differs from". If the subquery returns 1 and 2, then `1 <> ANY (...)` is TRUE, because 1 differs from 2. The predicate is therefore almost always TRUE and is essentially never what someone meant when they typed it — they wanted `NOT IN` / `<> ALL`. Treat `<> ANY` in a code review as a bug until proven otherwise. The same asymmetry runs the other way: `x = ALL (<subquery>)` means every returned row equals x, which can only be TRUE if the subquery returns a single distinct value (or no rows at all). ## Beyond equality The quantifiers are more expressive than `IN`, which is fixed to equality: ```sql -- products more expensive than every product in category 7 SELECT * FROM products p WHERE p.price > ALL (SELECT price FROM products WHERE category_id = 7); -- products more expensive than at least one of them SELECT * FROM products p WHERE p.price > ANY (SELECT price FROM products WHERE category_id = 7); ``` Read `> ALL` as "greater than the maximum" and `> ANY` as "greater than the minimum". The comparison with an aggregate form such as `> (SELECT MAX(price) ...)` is instructive but not identical, because the two forms diverge when the subquery is empty or contains NULLs. ## NULLs and the quantifiers Everything above sits inside three-valued logic. If the subquery returns 10, 20 and NULL, then `5 > ALL (...)` cannot be TRUE: `5 > 10` is FALSE, so the ALL is FALSE. But `50 > ALL (...)` is UNKNOWN, not TRUE — `50 > 10` and `50 > 20` are TRUE, while `50 > NULL` is UNKNOWN, and a universal claim with an undecided conjunct is undecided. On the ANY side, a single TRUE comparison still wins outright, so `50 > ANY (10, 20, NULL)` is TRUE. The general rule: for ANY, a TRUE anywhere dominates; for ALL, a FALSE anywhere dominates; otherwise a lurking UNKNOWN wins. ## Portability and style The quantified predicates have been in the standard since SQL-92, but not every engine implements `ANY`/`ALL` with subqueries — some lightweight engines offer only `IN` and `EXISTS` — so check your target before relying on them. Stylistically, prefer `IN` for equality membership, keep `> ALL`/`< ALL` for genuine max/min comparisons, and avoid `<> ANY` entirely; the readers of your query will thank you.
- Is `x <> ANY (SELECT y FROM t)` a valid way to write NOT IN?No, and it is a common bug. `<> ANY` is existential: it is TRUE if x differs from at least one returned row, so once the subquery returns two distinct values it is TRUE for practically every x, including values that are present in the subquery. The correct expansion of NOT IN is `<> ALL`. Treat `<> ANY` in review as an error unless someone can justify it.
- How would you read `price > ALL (SELECT price FROM products WHERE category_id = 7)` in plain English?"More expensive than every product in category 7" — that is, greater than the maximum of that set. The ANY form, `> ANY`, reads as "more expensive than at least one of them", i.e. greater than the minimum. The mnemonic is: ALL compares against the extreme that makes the universal claim hardest, ANY against the one that makes it easiest.
- What does `x = ALL (SELECT y FROM t)` mean, and when is it TRUE?It claims that x equals every row the subquery returns, so it can only be TRUE when the subquery yields a single distinct value equal to x — or no rows at all, where the universal claim is vacuously TRUE. Any second, different value makes one comparison FALSE and the whole predicate FALSE. It is rarely what an author intends.
saying these in an interview costs you the question
- Says `<> ANY` is the same as NOT IN
- Treats ANY and ALL as interchangeable emphasis words
- Believes IN and = ANY differ in meaning
- Reads `> ANY` as greater than every value
- Thinks ANY/ALL only work with the equality operator