ANY Operator
SQL · SQL Subqueries
Theory
ANY compares a value with the values returned by a subquery, and the condition is TRUE if it holds for at least one of them. salary > ANY (SELECT salary FROM interns) keeps employees who earn more than at least one intern, which means more than the lowest intern salary. SOME is an exact synonym for ANY.
With equality, = ANY (subquery) means exactly the same as IN (subquery). If the subquery returns no rows, ANY is FALSE for every outer row. If there is no match but the list contains a NULL, the result is UNKNOWN and the row is dropped. The equivalent salary > (SELECT MIN(salary) FROM interns) is often clearer and is what you write on engines without ANY, such as SQLite. Beware the English trap: <> ANY is TRUE if the value differs from at least one item, which is almost always TRUE; to mean "not in the list" use <> ALL or NOT IN.
Why we use it
Some rules only need to beat one value in a set: cheaper than at least one competitor, scored above any of the pass marks. ANY expresses "at least one" without writing a separate MIN or MAX query.
When to use it
Use ANY for "at least one" comparisons against a subquery, and = ANY interchangeably with IN. Prefer > MIN() or < MAX() when readability matters, and use ALL when every value must satisfy the condition.
How it works
- Take an outer row
- Run the subquery to get the list of values
- Compare the outer value with each item
- Does the comparison hold for at least one value? Yes: Keep the row. No: Discard the row.
- Move to the next row
Syntax
SELECT columns FROM table
WHERE column > ANY (subquery);Example
SELECT name FROM employees
WHERE salary > ANY (SELECT salary FROM interns);Key points
- SOME is a synonym for ANY
- True if the comparison holds against at least one returned row
- = ANY is the same as IN
- > ANY works like > MIN(); an empty subquery makes ANY FALSE
- <> ANY does not mean NOT IN; use <> ALL for that