ANY and ALL Operators
You'll learn how to compare values against multiple results from a subquery using the ANY and ALL operators.
What Are ANY and ALL?
When a subquery returns multiple values, the ANY and ALL operators let you test whether your comparison matches at least one or every single value in that result set.
- ANY: Returns
TRUEif the comparison is true for at least one value returned by the subquery - ALL: Returns
TRUEonly if the comparison is true for every value returned by the subquery
How They Work
Think of ANY like asking "Is this bigger than the shortest person in the room?" — you only need to beat one height. ALL is like "Is this bigger than the tallest person?" — you must beat everyone.
-- Find employees earning more than ANY salesperson
SELECT name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE job = 'Salesperson');
-- Find employees earning more than ALL salespeople
SELECT name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE job = 'Salesperson');
The first query finds employees earning more than at least one salesperson. The second finds employees earning more than every salesperson.
Common Comparison Operators
You can use ANY and ALL with >, <, >=, <=, =, and !=:
= ANY(...)is equivalent toIN(...)!= ALL(...)is equivalent toNOT IN(...)
NULL Handling
Be cautious: if the subquery contains NULLs, ALL comparisons may not behave as expected, potentially returning no rows.
Key Takeaway: Use ANY to match at least one subquery result and ALL to match every result — they transform multi-value comparisons into boolean tests.