SQL guide · Filtering

SQL ANY: True If Any Value in a Set Matches

The ANY operator compares a value against every row returned by a subquery and is true if the comparison holds for at least one of them. It is always paired with a comparison operator, as in = ANY, > ANY or <> ANY. The keyword SOME is a synonym.

The meaning depends on the operator. x = ANY (subquery) behaves exactly like x IN (subquery). x > ANY (subquery) is true when x is greater than at least one returned value, which is the same as being greater than the minimum. x < ANY (subquery) is true when x is below the maximum.

If the subquery returns no rows, ANY is false. If it contains NULLs, the result can be unknown rather than false when no non-NULL value satisfies the comparison. In practice many analysts rewrite ANY with MIN, MAX, IN or EXISTS, but ANY stays useful when the intent is clear or when passing arrays in PostgreSQL.

Basic syntax

SELECT column1
FROM table_name
WHERE column operator ANY (subquery);

-- operator is one of =, <>, <, <=, >, >=

The subquery must return a single column of a compatible type. ANY and SOME are interchangeable. Beware that <> ANY means "differs from at least one", which is not the same as NOT IN. For readability, many teams add a short comment explaining the intended meaning, since readers often mistake the operator for the opposite behavior.

SQL ANY examples

Equal to any value (same as IN)

Returns products whose id appears among items in orders above 500. This is an alternative spelling of IN with a subquery.

SELECT product_id, name
FROM products
WHERE product_id = ANY (
  SELECT product_id
  FROM order_items
  WHERE quantity > 500
);

Greater than any value

Employees who earn more than at least one person in the Support department. This equals comparing against the lowest support salary.

SELECT employee_id, name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary
  FROM employees
  WHERE department = 'Support'
);

Less than any value

Products priced below at least one premium item, meaning cheaper than the most expensive premium product. The comparison is with the maximum of the set.

SELECT product_id, name, price
FROM products
WHERE price < ANY (
  SELECT price FROM products WHERE tier = 'premium'
);

Equivalent rewrite with an aggregate

The same condition can be written with MIN, which many readers find clearer. The results are identical when the subquery has no NULLs.

SELECT employee_id, name, salary
FROM employees
WHERE salary > (
  SELECT MIN(salary)
  FROM employees
  WHERE department = 'Support'
);

Where SQL dialects differ

PostgreSQL: Supports ANY with subqueries and with arrays, for example WHERE id = ANY(ARRAY[1, 2, 3]) or = ANY($1) for a bound array parameter. SOME is accepted.

MySQL: Supports ANY and SOME with subqueries only, not arrays. Supported in versions 4.1 and later.

SQL Server: Supports ANY and SOME with subqueries returning one column. Arrays are not available; use IN with a table variable or STRING_SPLIT (2016 and later).

SQLite: Does not support ANY, SOME or ALL. Rewrite with IN, EXISTS, or MIN and MAX.

Oracle: Supports ANY and SOME with a subquery or a list of literals, such as salary > ANY (3000, 4000).

Common mistakes

Reading <> ANY as "not in the set". It is true if the value differs from at least one element, which is almost always true. Use NOT IN or <> ALL.
Forgetting the comparison operator in front of ANY. It cannot be used alone.
Ignoring NULL in the subquery, which can turn expected true or false results into unknown.
Assuming SQLite supports it. Rewrite the logic with EXISTS or aggregates there.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator