What do EXISTS, IN, ANY and ALL return when the subquery returns no rows?

## The rule in one table With `S` an empty subquery result: | predicate | result | |---|---| | `EXISTS (S)` | FALSE | | `NOT EXISTS (S)` | TRUE | | `x IN (S)` / `x = ANY (S)` | FALSE | | `x NOT IN (S)` / `x <> ALL (S)` | TRUE | | `x > ANY (S)`, `x < ANY (S)`, … | FALSE | | `x > ALL (S)`, `x < ALL (S)`, … | TRUE | Nothing here is UNKNOWN. Emptiness is a definite fact about the subquery, so all of these predicates give definite truth values regardless of nullability. ## Why ANY is FALSE and ALL is TRUE Think of the quantifiers as folds over the set of comparisons. `ANY` is a big `OR`; the identity of `OR` is FALSE, so an empty `OR` is FALSE. `ALL` is a big `AND`; the identity of `AND` is TRUE, so an empty `AND` is TRUE. Logicians call the second case *vacuous truth*: the claim "every element of the empty set satisfies P" has no counterexample, so it holds. The everyday phrasing helps too. `x NOT IN (S)` asks "is x absent from S?" — if S is empty, x is certainly absent, so TRUE. `x IN (S)` asks "is x present?" — it cannot be, so FALSE. That much is intuitive. The counterintuitive one is inequality: `x > ALL (S)` asks "is x greater than every element?" and with nothing to be greater than, the answer is yes. ## The bug this causes ```sql -- Intent: only products pricier than everything in category 7 SELECT p.id, p.name FROM products p WHERE p.price > ALL (SELECT price FROM products WHERE category_id = 7); ``` If category 7 has been emptied — a data-loading failure, a soft-delete flag, a tenant that has no such category — the subquery returns nothing, the predicate is vacuously TRUE, and the query returns the **entire** products table. A filter that was meant to be restrictive becomes a pass-through. Because it produces plausible-looking rows instead of an error, this is a silent wrong-answer defect, and it is a good thing to check whenever a report suddenly grows. ## The aggregate rewrite behaves differently A natural rewrite of the same intent is: ```sql WHERE p.price > (SELECT MAX(price) FROM products WHERE category_id = 7) ``` Over an empty input, `MAX` returns NULL, so the comparison is `price > NULL` → UNKNOWN, and `WHERE` keeps nothing. So the two spellings of "pricier than everything in category 7" disagree exactly on the empty case: the quantified form returns all rows, the aggregate form returns none. Neither is wrong; they encode different answers to "what should happen when there is nothing to compare against?", and part of writing a correct query is deciding which one you mean and making it explicit — for instance by guarding with `EXISTS (SELECT 1 FROM products WHERE category_id = 7)` when the empty case must yield nothing. ## EXISTS is the easy one `EXISTS` over an empty result is FALSE and `NOT EXISTS` is TRUE, which matches how people read them out loud, so this case rarely surprises anyone. It also composes safely: an anti-filter written with `NOT EXISTS` keeps every outer row when the inner set is empty, which is exactly the intended meaning of "has no match". ## Empty is not the same as containing NULL It is worth separating two situations that both involve "missing" data. A subquery that returns *no rows* produces definite TRUE/FALSE answers as tabulated above. A subquery that returns *a NULL row* is a different case: there is something to compare against, the comparison is undecidable, and the predicate can land on UNKNOWN. `x NOT IN (empty)` is TRUE; `x NOT IN (NULL)` is UNKNOWN. Candidates who conflate the two give the wrong answer to both. ## What to remember Empty means *definite*, never UNKNOWN. Existential predicates fail on empty; universal predicates succeed on empty. If a universal predicate is doing real filtering work in production code, ask what should happen when its subquery is empty and write that intent down — either with an explicit guard or with a comment — because the vacuous-truth default is the opposite of what most readers assume.