ANY / ALLQuery Techniques
Buy Me A Coffee

Subquery comparison operators. ANY (or SOME) returns true if the comparison holds for at least one value in the subquery result. ALL returns true only if the comparison holds for every value. Semantically related to EXISTS and IN but more flexible with comparison operators.

ANY compares a value against any element from a subquery or list (TRUE if any comparison is TRUE); ALL requires all comparisons to be TRUE. Useful for comparative filters against grouped or multi-row subquery results.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 4.1 Both ANY and ALL supported since 4.1. SOME is an alias for ANY. Works with =, <>, <, <=, >, >= operators. = ANY(...) is equivalent to IN(...). <> ALL(...) is equivalent to NOT IN(...). If the subquery returns an empty result, ANY returns false and ALL returns true (vacuous truth). NULL in the subquery results causes the same NULL-propagation issues as IN.
PostgreSQL ✓ Supported 6.0 Full ANY / ALL / SOME support with all comparison operators. PostgreSQL additionally supports ANY and ALL against arrays (not just subqueries): WHERE val = ANY(ARRAY[1,2,3]) is a common idiom. Array form is often more concise than IN() for literal lists.
SQL Server ✓ Supported 6.5 ANY and ALL supported but rarely used in practice. SOME is also accepted as an alias for ANY. SQL Server developers typically use IN / NOT IN / EXISTS patterns instead, which the optimizer handles equivalently. The array form (ANY(ARRAY[...])) is not supported — only the subquery form.
Oracle ✓ Supported 7 ANY and ALL supported with all comparison operators. SOME accepted as alias for ANY. Scalar subquery optimization applies. Like SQL Server, Oracle developers typically use IN / EXISTS / aggregate subquery patterns in practice, but ANY / ALL are fully valid.
SQLite ✗ Not Supported — ANY and ALL operators are not supported. Use IN, NOT IN, EXISTS, or subqueries with MIN/MAX aggregates as equivalents.

Details

= ANY(subquery) is exactly equivalent to IN(subquery); <> ALL(subquery) is equivalent to NOT IN(subquery). These relationships are useful for remembering the semantics. The practical mnemonic for choosing between ANY and ALL: ANY behaves like OR (at least one must match); ALL behaves like AND (every one must match). PostgreSQL extends ANY/ALL to work on arrays, which is a common and idiomatic PostgreSQL pattern not found elsewhere. SQLite is the only engine with no support — use MIN/MAX aggregate subqueries as the portable fallback.

Standard Syntax

-- ANY: true if salary > any value returned SELECT name FROM employees WHERE salary > ANY ( SELECT salary FROM employees WHERE dept = 'Sales' ); -- ALL: true only if salary > every value returned SELECT name FROM employees WHERE salary > ALL ( SELECT salary FROM employees WHERE dept = 'Interns' );

Version Support

MySQL: Since 4.1 PostgreSQL: Since 6.0 SQL Server: Since 6.5 Oracle: Since 7 SQLite: Not supported

Per-Database Syntax & Notes

MySQL

Both ANY and ALL supported since 4.1. SOME is an alias for ANY. Works with =, <>, <, <=, >, >= operators. = ANY(...) is equivalent to IN(...). <> ALL(...) is equivalent to NOT IN(...). If the subquery returns an empty result, ANY returns false and ALL returns true (vacuous truth). NULL in the subquery results causes the same NULL-propagation issues as IN.

-- ANY (true if any value matches): SELECT name FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE dept = 'Sales'); -- ALL (true if all values match): SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE dept = 'Interns'); -- = ANY is equivalent to IN: SELECT * FROM products WHERE category_id = ANY (SELECT id FROM categories WHERE active = 1); -- same as: WHERE category_id IN (SELECT id FROM categories WHERE active = 1) -- <> ALL is equivalent to NOT IN: SELECT * FROM orders WHERE status <> ALL (SELECT status FROM cancelled_statuses);

PostgreSQL

Full ANY / ALL / SOME support with all comparison operators. PostgreSQL additionally supports ANY and ALL against arrays (not just subqueries): WHERE val = ANY(ARRAY[1,2,3]) is a common idiom. Array form is often more concise than IN() for literal lists.

-- Subquery form: SELECT name FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE dept = 'Sales'); -- Array form (PostgreSQL extension): SELECT * FROM products WHERE category_id = ANY(ARRAY[1, 2, 3]); SELECT * FROM orders WHERE status <> ALL(ARRAY['cancelled', 'refunded']); -- Useful for parameterized queries: -- WHERE tag_id = ANY($1::int[]) (pass an array parameter)

SQL Server

ANY and ALL supported but rarely used in practice. SOME is also accepted as an alias for ANY. SQL Server developers typically use IN / NOT IN / EXISTS patterns instead, which the optimizer handles equivalently. The array form (ANY(ARRAY[...])) is not supported — only the subquery form.

-- ANY with subquery: SELECT name FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE dept = 'Sales'); -- ALL with subquery: SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE dept = 'Interns'); -- More common SQL Server idiom (equivalent): SELECT name FROM employees WHERE salary > (SELECT MIN(salary) FROM employees WHERE dept = 'Interns'); -- (MIN achieves the same as > ALL; MAX achieves the same as > ANY)

Oracle

ANY and ALL supported with all comparison operators. SOME accepted as alias for ANY. Scalar subquery optimization applies. Like SQL Server, Oracle developers typically use IN / EXISTS / aggregate subquery patterns in practice, but ANY / ALL are fully valid.

-- ANY: SELECT name FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE dept = 'Sales'); -- ALL: SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE dept = 'Interns'); -- Equivalent using aggregate: SELECT name FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE dept = 'Interns'); -- (MAX(x) = x > ALL subquery; MIN(x) = x > ANY subquery)

SQLite

ANY and ALL operators are not supported. Use IN, NOT IN, EXISTS, or subqueries with MIN/MAX aggregates as equivalents.

-- Instead of > ANY(subquery), use > MIN(...): SELECT name FROM employees WHERE salary > (SELECT MIN(salary) FROM employees WHERE dept = 'Sales'); -- Instead of > ALL(subquery), use > MAX(...): SELECT name FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE dept = 'Interns'); -- Instead of = ANY, use IN: SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1);