Course contentsShow
← Data Science
Lesson 963 of 2,14525. SQL Subqueries and CTEsFree lesson

ANY and ALL Operators

Compare values against subquery results using ANY (match at least one) and ALL (match every value) operators.

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 TRUE if the comparison is true for at least one value returned by the subquery
  • ALL: Returns TRUE only 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 to IN(...)
  • != ALL(...) is equivalent to NOT 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.