# SQL ANY: True If Any Value in a Set Matches

- URL: https://betteranalyst.com/sql/any

SQL guide · Filtering

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

- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL ALL](https://betteranalyst.com/sql/all)
- [SQL IN](https://betteranalyst.com/sql/in)
- [SQL EXISTS](https://betteranalyst.com/sql/exists)
- [SQL AND](https://betteranalyst.com/sql/and)
- [SQL SELECT](https://betteranalyst.com/sql/select)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)