ANY and ALL Operators

ANY Means At Least One; ALL Means Every

ANY and ALL compare one scalar expression with a one-column subquery. SOME is a synonym for ANY.

Verified Comparison Set

Using the shared sq_students table, X-A contains known marks 86.50 and 91.00. X-B contains 74.00, 88.50 and NULL. The examples filter NULL explicitly when the requirement is about known marks.

SELECT marks
FROM sq_students
WHERE class_name = 'X-A'
  AND marks IS NOT NULL;

Greater Than ANY X-A Mark

SELECT student_name, marks
FROM sq_students
WHERE marks > ANY (
  SELECT marks
  FROM sq_students
  WHERE class_name = 'X-A'
    AND marks IS NOT NULL
)
ORDER BY marks DESC;

Meera is greater than 86.50; Sana is also greater than 86.50. Aarav equals the lower member rather than exceeding it. Kabir is lower than both and Vihaan's outer comparison is UNKNOWN.

Greater Than Every X-B Known Mark

SELECT student_name, marks
FROM sq_students
WHERE marks > ALL (
  SELECT marks
  FROM sq_students
  WHERE class_name = 'X-B'
    AND marks IS NOT NULL
)
ORDER BY student_id;

The known X-B maximum is 88.50. Meera exceeds both 74.00 and 88.50. Sana equals 88.50, so the strict greater-than comparison fails.

Relations to IN, MIN and MAX

-- Equivalent membership forms:
WHERE class_name = ANY (
  SELECT class_name FROM allowed_classes
)

WHERE class_name IN (
  SELECT class_name FROM allowed_classes
)

For a non-empty, NULL-free numeric set, x > ANY(set) behaves like x > MIN(set), and x > ALL(set) behaves like x > MAX(set). Those rewrites are not automatically equivalent for empty sets or NULL-containing sets, so encode the business rule rather than applying a slogan.

Empty Sets and NULL Values

• For an ordinary non-NULL comparison, ANY over an empty result is FALSE.
• ALL over an empty result is TRUE: no returned row disproves the condition.
• If comparisons include NULL and no TRUE/FALSE result settles the predicate, the outcome can be UNKNOWN.
• WHERE keeps only TRUE, not FALSE or UNKNOWN.

-- Known marks only:
WHERE marks > ALL (
  SELECT marks
  FROM sq_students
  WHERE class_name = 'X-B'
    AND marks IS NOT NULL
)

Restrictions, Debugging and Practice

For expr op {ANY|ALL|SOME} subquery, MySQL expects a scalar expression and a one-column subquery. Multi-column row comparison is not supported for these operators in the same way that row constructors are supported with IN.

1. Run the subquery and record min, max, NULL count and row count.
2. Translate the quantifier into plain language.
3. Test equality at the boundary, not only values above and below it.
4. Test empty and NULL-containing sets.
5. Use EXPLAIN after correctness.

Practice: find scores lower than ANY X-A mark; lower than ALL X-A marks; rewrite = ANY as IN; and explain the result when the inner class does not exist.

Official References

• MySQL 8.4: ANY, IN and SOME
• MySQL 8.4: ALL Subqueries
• MySQL 8.4: Subquery Restrictions

Syntax and behavior were checked against the official MySQL 8.4 manual. Test every query on a disposable copy before production use.