SQL ANY

ANY compares a value against every row a subquery returns, and is true if the comparison holds for at least one of them. Unlike EXISTS, which ignores the subquery's actual values, ANY compares against them directly.

Learning Objectives

Use > ANY, < ANY, and = ANY against a subquery's results.
Recognize = ANY as equivalent to IN.
Know which database in this course doesn't support ANY at all.

Basic ANY

This returns every employee earning more than at least one Marketing employee (department_id 3), which in practice just means earning more than the lowest Marketing salary. salary > ANY (subquery) is true as soon as the comparison holds against any single row the subquery returns.

MySQL / MariaDB / PostgreSQL / SQL Server / Oracle

SELECT first_name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary FROM employees WHERE department_id = 3
);

= ANY Is the Same as IN

= ANY (subquery) and IN (subquery) mean exactly the same thing, true if the value equals at least one row the subquery returns. ANY's real value is everywhere IN can't go, comparisons other than equality, like > ANY above.

These two are equivalent

-- Using = ANY
SELECT first_name FROM employees WHERE department_id = ANY (SELECT id FROM departments WHERE name IN ('Engineering', 'Research'));

-- Using IN
SELECT first_name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name IN ('Engineering', 'Research'));

SQLite Doesn't Support ANY

Database	ANY Support
MySQL / MariaDB / PostgreSQL / SQL Server / Oracle	Supported
SQLite	Not supported, rewrite using MIN()/MAX() instead

SQLite-compatible rewrite of > ANY

SELECT first_name, salary
FROM employees
WHERE salary > (
  SELECT MIN(salary) FROM employees WHERE department_id = 3
);

NOTE

> ANY (subquery) means exactly "greater than the smallest value the subquery returns." Replacing ANY with an aggregate (MIN() here) is both SQLite-compatible and, for a value already known to be fixed like this, often clearer to a reader than ANY itself.

Try It Yourself

Write a query, portable to SQLite, that returns employees earning more than at least one Research department employee.

Answer

SELECT first_name, salary
FROM employees
WHERE salary > (
  SELECT MIN(salary) FROM employees WHERE department_id = 2
);

Frequently Asked Questions

Is SOME a synonym for ANY?

Yes, on the databases that support ANY, SOME means exactly the same thing, purely a readability alternative with identical behavior.

What does ANY return if the subquery returns zero rows?

False, unconditionally, there's nothing for the comparison to match against. This is different from NOT IN's NULL danger, an empty subquery with ANY is simply false, not a silent full-table exclusion.

Common Beginner Mistakes

Assuming > ANY means "greater than every value"

It means the opposite, greater than at least one value, equivalent to greater than the minimum. "Greater than every value" is what ALL does, covered next.

Using ANY on SQLite and hitting a syntax error

SQLite simply doesn't implement ANY/ALL. Rewrite using MIN()/MAX() as shown above, it's both portable and often clearer.

Best Practices

Use = ANY only for consistency with other ANY comparisons nearby, IN reads more clearly on its own for plain equality.
Prefer a MIN()/MAX() rewrite over ANY/ALL when portability to SQLite matters.
Reserve ANY for genuinely needing "matches at least one" with a non-equality comparison.

Interview Questions

What does salary > ANY (subquery) actually mean?

It's true if salary is greater than at least one value the subquery returns, equivalent to salary being greater than the subquery's minimum result. It does not mean greater than every value returned.

How would you rewrite salary > ANY (SELECT salary FROM employees WHERE department_id = 3) to work on SQLite?

SELECT ... WHERE salary > (SELECT MIN(salary) FROM employees WHERE department_id = 3); — replacing ANY with MIN() produces the identical result and works on every database in this course, including SQLite.

Summary

ANY compares a value against a subquery's results and is true if the comparison matches at least one row, equivalent to comparing against MIN() for > and >=, or MAX() for < and <=. = ANY is identical to IN. SQLite doesn't support it at all, a MIN()/MAX() subquery is the portable rewrite.

What's Next?

ALL next, ANY's counterpart, true only when a comparison holds against every row a subquery returns, not just one.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.