∀ANY and ALL

Quantified comparisons to a subquery result

scalar op ANY (subquery) is true if at least one row in the subquery makes the comparison true, logically an OR over the inner multiset. scalar op ALL (subquery) requires the comparison to hold for every row, an AND over the multiset. These are not the same as IN / NOT IN unless you use = ANY / <> ALL with care around NULLs.

SOME is a standard synonym for ANY. Prefer ANY in shared code unless your style guide likes the English word.

SQL

ANY: run beats at least one rule threshold

SELECT run_id, max_qscore
FROM qc_metrics
WHERE max_qscore > ANY (
  SELECT threshold FROM qc_rules WHERE assay = 'WGS'
);

✓Illustrative result set

run_id	max_qscore
R-10	38.2
R-11	41

SQL

ALL: batch must meet every target on its line

SELECT batch_id
FROM batch_qc b
WHERE b.failure_rate <= ALL (
  SELECT target FROM batch_targets WHERE line = b.line
);

✓Illustrative result set

batch_id
B-900

∅Empty inner set (SQL standard corner cases)

Interview favorite: verify your engine matches the standard when it matters.

Form	Typical SQL standard result
x > ALL (empty)	Vacuously TRUE (no counterexample row)
x > ANY (empty)	FALSE (no witness row)

⚡ Advanced / dialect note

When the inner subquery can yield NULL in the compared column, each comparison involving NULL is UNKNOWN in three-valued logic-ALL can collapse to UNKNOWN in ways that surprise people who expected “vacuous truth.” Test with NULL fixtures or normalize NULLs out of the inner set.

= ANY (subquery) without understanding duplicates

Duplicates do not change ANY truth, but they can confuse readers and interact oddly with ALL when combined with NULLs.

DISTINCT inner set or EXISTS when you mean “at least one distinct code”

Wrap the inner query in SELECT DISTINCT … when the set should be a true set of rule ids, or rewrite as EXISTS / join when that matches the business sentence.

When ANY/ALL shine

Threshold catalogs loaded per assay line, per jurisdiction, or per instrument model, small inner sets, clear universal (“all targets met”) or existential (“beats any historical bad run”) English mapping.

When to rewrite

Large correlated inner sets, ORM-generated SQL, or readers unfamiliar with quantifiers, often clearer as a join plus GROUP BY / window MIN / MAX with an explicit comparison.

Readability

If the same logic takes you more than a minute to explain in code review, prefer an explicit CTE that names the inner multiset (rule_thresholds) before the ANY/ALL predicate, future you will thank present you.

TL;DR

x op ANY (subq) is true if the comparison holds for at least one row returned by the subquery; x op ALL (subq) requires it for every row. = ANY is equivalent to IN when the subquery cannot produce NULLs; with NULLs, three-valued logic makes NOT IN and NOT IN (subq) easy to get wrong, often prefer EXISTS / NOT EXISTS for clarity and predictable NULL behavior.