SQL ANY and ALL Operators Explained with Examples

ANY and ALL compare a value with each row of a subquery, one comparison per row. ANY is true if at least one comparison is true, and ALL if every comparison is. For example, you can find the students who are older than every student of another class.

A plain > can't compare with several rows: it accepts a subquery of one row only. Given two rows, PostgreSQL stops with "more than one row returned by a subquery used as an expression".

Both operators have the same syntax:

expression operator ANY (subquery)
expression operator ALL (subquery)

• expression is the value to compare, usually a column of the outer query.
• operator is one of =, <>, <, <=, > and >=.
• ANY is true when the comparison holds for at least one row. SOME is another name for ANY.
• ALL is true when the comparison holds for every row.
• subquery is a SELECT that returns one column.

Create a sample table of four students in two classes:

CREATE TABLE students (
  name  VARCHAR(20),
  class CHAR(1),
  age   INT
);

INSERT INTO students VALUES
  ('Alice',   'A', 21),
  ('Bob',     'A', 22),
  ('Charlie', 'B', 18),
  ('Dave',    'B', 21);

Every statement below was run on PostgreSQL 18 and MySQL 9.1, and returned the same rows on both. SQLite has neither operator, and rejects both with a syntax error.

ANY

ANY is true when the comparison holds for at least one row of the subquery. For example, you can list the students who are older than at least one student of class B.

SELECT name, age
FROM students
WHERE age > ANY (SELECT age FROM students WHERE class = 'B');

The subquery returns 18 and 21. Alice, Bob and Dave are older than Charlie's 18, and one true comparison is enough. Charlie is older than nobody in class B, so his row is missing.

ALL

ALL is true when the comparison holds for every row of the subquery. For example, you can list the students who are older than every student of class B.

SELECT name, age
FROM students
WHERE age > ALL (SELECT age FROM students WHERE class = 'B');

Bob's 22 is above both 18 and 21. Alice and Dave are 21, and 21 is not above 21, so ALL is false for both of them.

> ANY therefore keeps every age above the smallest value of the subquery, and > ALL every age above the largest. ALL straight after SELECT is another keyword: SELECT ALL keeps duplicate rows, which SELECT does by default (PostgreSQL 18).

= ANY and IN

= ANY is true when the value equals at least one row of the subquery. For example, you can list the students whose age also occurs in class B.

SELECT name, age
FROM students
WHERE age = ANY (SELECT age FROM students WHERE class = 'B');

Alice is in class A, but her 21 equals Dave's 21. age IN (SELECT ...) returns the same three rows as = ANY. PostgreSQL and MySQL both document IN as = ANY, and IN is shorter.

<> ALL and NOT IN

<> ALL is true when the value differs from every row of the subquery. For example, you can list the students whose age occurs nowhere in class B.

SELECT name, age
FROM students
WHERE age <> ALL (SELECT age FROM students WHERE class = 'B');

Only Bob's 22 differs from both 18 and 21. MySQL and PostgreSQL document NOT IN as the same as <> ALL, so age NOT IN (SELECT ...) returns Bob too.

ALL in HAVING

In HAVING, ALL compares a value of each group, such as MAX(age), with every row of the subquery. For example, you can find the class that has the oldest student.

SELECT class, MAX(age) AS oldest
FROM students
GROUP BY class
HAVING MAX(age) >= ALL (SELECT age FROM students);

Class A's oldest student is 22, and 22 is greater than or equal to every age in the table. Class B's oldest is 21, which is below Bob's 22.

What goes wrong

<> ANY is not NOT IN

<> ANY is true when the value differs from at least one row. Class B holds 18 and 21, and every student differs from one of them:

SELECT name, age
FROM students
WHERE age <> ANY (SELECT age FROM students WHERE class = 'B');

Charlie's 18 differs from 21, and the other three differ from 18. The English phrase "not equal to any" suggests "equal to none", and MySQL's manual warns about that reading. To exclude values, use <> ALL or NOT IN.

An empty subquery

Over a subquery that returns no rows, ALL is true and ANY is false (PostgreSQL 18). No student is in class C:

SELECT name, age
FROM students
WHERE age > ALL (SELECT age FROM students WHERE class = 'C');

> ANY over the same subquery returns no rows. age > (SELECT MAX(age) ...) returns no rows too, because MAX over no rows is NULL. So over an empty subquery, > ALL and a comparison with MAX return different rows.

A NULL in the subquery

Add a student without an age to class B, and look at the rows that the subquery now reads:

INSERT INTO students VALUES ('Eve', 'B', NULL);

SELECT name, age FROM students WHERE class = 'B';

Now age > ALL (SELECT age FROM students WHERE class = 'B') returns no rows. Bob's 22 is above 18 and 21, but 22 compared with NULL is unknown, neither true nor false. WHERE keeps only the rows whose condition is true, so Bob is dropped.

NOT IN over the same subquery also returns no rows. Filter the NULL out of the subquery with AND age IS NOT NULL, and Bob comes back.

A list of values in place of a subquery

IN takes a list such as IN (18, 21), but ANY and ALL take a list only in some databases:

PostgreSQL documents the array form under array comparisons, and Oracle the list form under comparison conditions. SQL Server's grammar takes a subquery only. SQLite 3.50.4 rejects age > ANY (SELECT ...) with near "SELECT": syntax error, so use IN, NOT IN, MIN or MAX there.

Run the queries in DbSchema

The SQL Editor in DbSchema runs a query and shows its rows in a grid under the editor. Press Editor in the toolbar, choose SQL Editor, paste a query from this article, and press Run.

When a result looks wrong, select the subquery and press Run. DbSchema runs only the selected text, so you see whether the subquery returns no rows or a NULL.

Download DbSchema at https://dbschema.com/download.html and connect it to PostgreSQL, MySQL, SQL Server or Oracle. Paste the CREATE TABLE and INSERT statements into the SQL Editor, then run each query and change it. The free Community Edition includes the database connection and the SQL Editor.