SQL ANY and ALL Operators Explained with Examples

SQL's ANY and ALL operators compare one value against every row a subquery returns. ANY is true when the comparison holds for at least one of those rows. ALL is true only when it holds for every one of them. Both must follow a comparison operator[1] - =, <>, >, >=, < or <= - and both take a subquery, never a list of literals.

What the SQL ANY and ALL operators do

Both operators sit between a value and a subquery. The engine evaluates the subquery, then compares the value on the left against each row it returned, one at a time, and folds the results into a single true, false or unknown.

• ANY: true if the comparison is true for at least one value in the subquery result set.
• ALL: true only if the comparison is true for every value in the subquery result set.
• SOME: a synonym for ANY[2]. The two keywords are interchangeable.

ANY vs ALL at a glance

Neither operator stands on its own. Each one needs a comparison operator in front of it and a single-column subquery behind it, which is why ANY and ALL are read as a pair with the operator that precedes them: > ALL, = ANY, <> ALL.

Syntax and the example table

The two forms are the same shape. Put the comparison operator first, then ANY or ALL, then a subquery returning one column. Every example on this page is a SELECT statement over the same small table, so only the operator changes from section to section.

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);

The Students table used in every example below:

Greater than ALL

Age > ALL (subquery) is true for a student whose age beats every age the subquery returned. With one row in the subquery it reads like a plain comparison; with several it is the same as comparing against the maximum.

SELECT Name
FROM Students
WHERE Age > ALL (SELECT Age FROM Students WHERE Name = 'Charlie');

Result:

Alice, Bob and Dave are all older than Charlie's 18, so all three rows pass.

Equal to ALL in WHERE and HAVING

ALL is not tied to the WHERE clause. The same condition works in a HAVING clause over grouped rows, because both clauses take a boolean expression and ALL produces one.

SELECT Name
FROM Students
WHERE Age = ALL (SELECT Age FROM Students WHERE Name = 'Charlie');

Result:

Only Charlie matches. The subquery returns a single age, 18, and = ALL demands the compared value equal every value returned. Add a second, different age to that subquery and the condition becomes unsatisfiable - no value can equal two different numbers at once.

Not equal to ALL and NOT IN

There is no NOT ALL operator. ALL has to follow a comparison operator, so the way to negate an equality test against a whole set is <> ALL, which is exactly what NOT IN means. MySQL documents NOT IN as an alias for <> ALL[1], and PostgreSQL states the same equivalence[4].

SELECT Name
FROM Students
WHERE Age <> ALL (SELECT Age FROM Students WHERE Name = 'Charlie');

-- the same query, written with NOT IN
SELECT Name
FROM Students
WHERE Age NOT IN (SELECT Age FROM Students WHERE Name = 'Charlie');

Result, from either form:

Alice, Bob and Dave all have an age that differs from Charlie's.

Greater than ANY

Age > ANY (subquery) is true as soon as one row in the subquery is smaller than the value on the left. Over several rows it is the same as comparing against the minimum - the mirror image of > ALL.

SELECT Name
FROM Students
WHERE Age > ANY (SELECT Age FROM Students WHERE Name = 'Charlie');

Result:

With a single-row subquery, > ANY and > ALL agree. They part company as soon as the subquery returns more than one row. Compare against Dave's age instead:

SELECT Name
FROM Students
WHERE Age > ANY (SELECT Age FROM Students WHERE Name = 'Dave');

Result:

Only Bob is older than Dave's 21.

Less than ANY

Reverse the comparison operator and the reading reverses with it. Age < ANY (subquery) is true when the value is below at least one of the ages returned, which over several rows means below the maximum.

SELECT Name
FROM Students
WHERE Age < ANY (SELECT Age FROM Students WHERE Name = 'Alice');

Result:

Only Charlie is younger than Alice's 20.

Equal to ANY and IN

= ANY is the membership test. It is true when the value equals at least one row of the subquery, and it is the same thing the IN operator does over a subquery - MySQL calls IN an alias for = ANY[3], and PostgreSQL documents IN as equivalent[4] to = ANY.

SELECT Name
FROM Students
WHERE Age = ANY (SELECT Age FROM Students WHERE Name = 'Charlie' OR Name = 'Dave');

Result:

One difference survives the equivalence: IN also accepts a written-out list of values, and = ANY does not - it always needs a subquery.

Not equal to ANY

<> ANY means "differs from at least one row in the subquery". This is the form readers most often misread as NOT IN, and it is not: NOT IN is an alias for <> ALL, never for <> ANY[3].

SELECT Name
FROM Students
WHERE Age <> ANY (SELECT Age FROM Students WHERE Name = 'Charlie');

Result:

The subquery returns one age, so every student whose age is not 18 passes. Widen the subquery to two different ages and <> ANY becomes true for every row in the table, including the two students whose ages are in the list - each of them still differs from the other value. That is the trap: <> ANY excludes nothing once the subquery holds more than one distinct value.

ANY and ALL versus IN and EXISTS

Three constructs cover the same ground, and picking between them is a readability decision far more often than a performance one.

The practical difference is what the subquery has to return. ANY and ALL compare a value against a single column of results, so the subquery must produce that column. The EXISTS operator ignores what the subquery selects and only asks whether it produced a row, which is why it pairs naturally with a correlated subquery and why NULL rows do not disturb it.

Which form runs faster is not a property of the keyword. PostgreSQL and MySQL both rewrite these constructs into the same semi-join where they can, so the execution plan decides. Read the plan for both rewrites against your own data instead of choosing on reputation - DbSchema's SQL Editor has an Explain button that renders the plan for the typed statement as a tree.

NULLs and empty subqueries

Two edge cases change the answer without changing the query, and both vendors call them out[1] by name. An empty subquery and a subquery carrying NULL values do not behave the way the plain-language reading suggests.

PostgreSQL states the empty case for both: the result of ALL is true if all rows yield true, including the case where the subquery returns no rows[4], while ANY is false if no true result is found, including that same case. MySQL documents the identical behaviour[1] - 1 > ALL (SELECT s1 FROM t2) is true when t2 is empty, and the ANY form is false.

NULLs are the second trap, and the rule is not "NULL makes it false". PostgreSQL is explicit: if there are no successes and at least one right-hand row yields null[4], the result of ANY is null, not false; and ALL is null when no comparison returns false and at least one returns null. A WHERE clause keeps only rows whose condition is true, so an unknown drops the row just as a false would - but NOT wrapped around that condition does not flip it back.

The defence is the same in every engine: filter NULLs out of the subquery when they carry no meaning for the comparison, and check whether the subquery can legitimately return zero rows before relying on an ALL condition.

-- an ALL condition that cannot be surprised by a NULL or an empty set
SELECT Name
FROM Students
WHERE Age > ALL (
    SELECT Age FROM Students
    WHERE Name = 'Charlie' AND Age IS NOT NULL
);

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Test ANY and ALL safely in DbSchema

An ANY or ALL condition is two queries stacked on one another, and most wrong results come from the inner one. Run the subquery on its own first and read the rows it returns. That tells you at once whether the set is empty, whether it carries a NULL, and whether it is the set you meant to compare against. Then wrap it in the outer comparison and run that.

DbSchema's SQL Editor runs a statement against the connected database and shows the rows in a grid with an execution log, so both steps take one keystroke each. The SQL editor is part of the free Community Edition. The editor does not auto-commit, so a statement you run while experimenting stays open until you press Commit or Rollback.

Common mistakes

• Reading <> ANY as NOT IN. NOT IN is <> ALL. <> ANY excludes nothing once the subquery holds more than one distinct value.
• Expecting an empty subquery to make an ALL condition fail. It makes it true, so a filter that silently returns no rows stops filtering.
• Assuming a NULL in the subquery behaves like a missing row. It turns the result unknown, which a WHERE clause drops but a NOT around it does not rescue.
• Writing NOT ALL or NOT ANY as if they were operators. Negate with the comparison operator instead: <> ALL, <> ANY.
• Using ANY or ALL without a subquery. Both require one; a written-out list of values is IN's job.

FAQs

Q: Can ANY and ALL be used with operators other than >, < and =? A: Yes. SQL Server's grammar[2] for SOME and ANY lists =, <>, !=, >, >=, !>, <, <= and !<, and ALL takes the same set.

Q: Are ANY and IN the same? A: Over a subquery, x = ANY (subquery) and x IN (subquery) return the same result. They diverge in two places: IN also accepts a literal list, and the negations are not parallel - NOT IN is <> ALL, not <> ANY.

Q: What is the difference between SOME and ANY? A: Nothing. SOME is a synonym for ANY in PostgreSQL, MySQL and SQL Server alike, and the three engines document it as such.

Q: Which databases support ANY and ALL? A: This page's behaviour was checked against the current PostgreSQL, MySQL and SQL Server documentation. Other engines support the operators too, but confirm the NULL and empty-subquery rules in your own engine's manual before relying on them.

Practice questions

1. Write a query returning students older than any student named "Dave".
2. Write a query returning the names of the students with the minimum age in the Students table, using ALL rather than a MIN aggregate.
3. Rewrite Age <> ALL (SELECT Age FROM Students WHERE Name = 'Bob') using NOT IN, and confirm both return the same rows.
4. Add a row to Students with a NULL age, then predict and check what Age > ALL (SELECT Age FROM Students) returns.
5. Point an ALL condition at a subquery whose WHERE clause matches nothing, and explain the result you get.

Download DbSchema and run these queries against your own schema: connect, open the SQL Editor, run the subquery, then run the full ANY or ALL condition over it. Connecting to the database, reverse-engineering it into an interactive diagram and the SQL editor are all in the free Community Edition; the visual query builder that assembles joins and subqueries by clicking is a Pro feature.

Sources

1. MySQL 8.4 Reference Manual: Subqueries with ALL
2. SOME | ANY (Transact-SQL) - SQL Server
3. MySQL 8.4 Reference Manual: Subqueries with ANY, IN, or SOME
4. PostgreSQL 17 documentation: Subquery Expressions