SQL ANY and ALL

SQL’s ANY and ALL keywords let you compare a single value against an entire set of values returned by a subquery, instead of against just one hardcoded value. ANY (its exact synonym is SOME) asks “is this comparison true for at least one row the subquery returns?” while ALL asks “is this comparison true for every row the subquery returns?” They are most useful when the list you want to compare against is itself the result of a query — for example, employees who earn more than everyone in another department — rather than a value you already know. This lesson covers the standard syntax, walks through exactly how a database evaluates it, and shows the portable rewrites you will reach for most often, since not every database (including the SQLite engine used to check the code on this page) implements ANY/ALL directly.

Overview: How ANY and ALL Work

ANY and ALL always appear immediately after a comparison operator (=, <>, <, <=, >, >=) and immediately before a subquery in parentheses. The subquery must return a single column, and it can return zero, one, or many rows. Conceptually, the database expands the comparison into a chain of OR (for ANY) or AND (for ALL) conditions, one per row the subquery returns:

x > ANY (subquery) means x > row1 OR x > row2 OR x > row3 ... — true as soon as one comparison succeeds.
x > ALL (subquery) means x > row1 AND x > row2 AND x > row3 ... — true only if every comparison succeeds.

Two consequences fall directly out of that definition. First, if the subquery returns zero rows, ANY is always false (there is nothing to satisfy) and ALL is always true (there is nothing to violate — a “vacuous truth”). Second, = ANY is exactly what the IN operator does, and <> ALL is exactly what NOT IN does — the SQL standard actually defines IN in terms of = ANY. That overlap matters in practice: most engines optimize = ANY/IN into a semi-join lookup (often index-backed) rather than literally testing row by row, and they optimize > ALL/< ALL/> ANY/< ANY into a single comparison against the subquery’s MIN() or MAX(), because that is mathematically equivalent and far cheaper than a per-row scan.

A portability note that matters for this course: ANY, SOME, and ALL are part of the SQL standard and are supported by MySQL, PostgreSQL, and SQL Server. SQLite — the engine that runs every example on this site — does not implement these keywords; running one raises a syntax error. Because the logic behind ANY/ALL is simple, there is always a portable, SQLite-compatible rewrite using IN, NOT IN, MIN(), or MAX(). The table below is the cheat sheet used throughout this lesson.

Standard SQL (MySQL / PostgreSQL / SQL Server)	Portable / SQLite-compatible rewrite
`x = ANY (subquery)`	`x IN (subquery)`
`x <> ALL (subquery)`	`x NOT IN (subquery)`
`x > ANY (subquery)`	`x > (SELECT MIN(col) FROM ...)`
`x > ALL (subquery)`	`x > (SELECT MAX(col) FROM ...)`
`x < ANY (subquery)`	`x < (SELECT MAX(col) FROM ...)`
`x < ALL (subquery)`	`x < (SELECT MIN(col) FROM ...)`

Syntax

The general form places ANY, SOME, or ALL right after a comparison operator, followed by a parenthesized subquery:

SELECT column_list
FROM table_name
WHERE expr comparison_operator ANY (subquery);

SELECT column_list
FROM table_name
WHERE expr comparison_operator ALL (subquery);

expr — the column or expression on the left, evaluated once per row of the outer query.
comparison_operator — one of =, <> / !=, <, <=, >, >=.
ANY / SOME — true if the comparison holds for at least one row returned by the subquery. SOME is a synonym with identical behavior.
ALL — true if the comparison holds for every row returned by the subquery.
subquery — a SELECT that returns exactly one column; it may return any number of rows, including zero.

Examples

Example 1: ANY — salary greater than at least one Marketing employee

In MySQL, PostgreSQL, or SQL Server you could write this with ANY directly:

SELECT name, department, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Marketing')
ORDER BY salary DESC;

SQLite does not accept that syntax, but since x > ANY (subquery) is equivalent to comparing against the subquery’s minimum value, the same result is produced by:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Alice', 'Engineering', 95000),
  (2, 'Bob', 'Marketing', 52000),
  (3, 'Carol', 'Marketing', 61000),
  (4, 'Dan', 'Sales', 58000),
  (5, 'Eve', 'Engineering', 88000);

SELECT name, department, salary
FROM employees
WHERE salary > (SELECT MIN(salary) FROM employees WHERE department = 'Marketing')
ORDER BY salary DESC;

Result:

name	department	salary
Alice	Engineering	95000
Eve	Engineering	88000
Carol	Marketing	61000
Dan	Sales	58000

The Marketing salaries are 52000 and 61000, so the minimum is 52000. Every employee earning more than 52000 qualifies — Bob himself is excluded because his own salary equals the minimum rather than exceeding it. ANY is a low bar: a row only needs to beat the smallest value in the subquery, not every value.

Example 2: ALL — salary greater than every Marketing employee

SELECT name, department, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Marketing')
ORDER BY salary DESC;

The SQLite-compatible rewrite compares against the maximum instead of the minimum:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Alice', 'Engineering', 95000),
  (2, 'Bob', 'Marketing', 52000),
  (3, 'Carol', 'Marketing', 61000),
  (4, 'Dan', 'Sales', 58000),
  (5, 'Eve', 'Engineering', 88000);

SELECT name, department, salary
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department = 'Marketing')
ORDER BY salary DESC;

Result:

name	department	salary
Alice	Engineering	95000
Eve	Engineering	88000

The Marketing maximum is 61000, so only employees earning strictly more than that survive: Alice and Eve. Carol and Dan, who both passed the ANY test in Example 1, fail here because ALL demands beating every Marketing salary, not just the smallest one. Switching ANY to ALL with the same operator never returns more rows — only the same or fewer.

Example 3: = ANY — the same thing as IN

When the operator is =, ANY stops being interesting on its own, because it is defined to behave exactly like IN:

SELECT product_name, price, category
FROM products
WHERE product_name = ANY (SELECT product_name FROM discontinued)
ORDER BY product_name;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  price REAL,
  category TEXT
);

INSERT INTO products (product_id, product_name, price, category) VALUES
  (1, 'Laptop', 1200, 'Electronics'),
  (2, 'Keyboard', 45, 'Electronics'),
  (3, 'Desk Lamp', 30, 'Home'),
  (4, 'Monitor', 300, 'Electronics'),
  (5, 'Office Chair', 150, 'Home');

CREATE TABLE discontinued (product_name TEXT);

INSERT INTO discontinued (product_name) VALUES ('Keyboard'), ('Desk Lamp');

SELECT product_name, price, category
FROM products
WHERE product_name IN (SELECT product_name FROM discontinued)
ORDER BY product_name;

Result:

product_name	price	category
Desk Lamp	30	Home
Keyboard	45	Electronics

Because there is no cheaper single-value stand-in for equality the way MIN/MAX stand in for >/<, engines that lack native ANY simply use IN — and in practice, even engines that do support = ANY almost always see it written as IN because it reads more naturally.

How It Works Step by Step (Under the Hood)

Given a query like SELECT ... FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Marketing'), the engine follows the usual logical processing order, with the subquery resolved as part of evaluating the WHERE clause:

Inner FROM/WHERE first: the subquery runs to completion, scanning employees filtered to department = 'Marketing' and producing the column of salaries it needs.
Materialize or rewrite: the planner decides how to use that result. For ANY/= it typically builds a hash or index lookup (the same plan it would use for IN). For ALL/> (or ANY/>) it typically collapses the subquery to a single aggregate — MAX() or MIN() — so the outer comparison becomes a plain scalar test instead of a repeated scan.
Outer FROM: the engine scans (or index-seeks) the outer table, employees in this case.
Outer WHERE: for each outer row, it applies the resolved comparison — salary > 61000, for example — keeping rows where the condition is true.
SELECT, ORDER BY, LIMIT: the surviving rows are projected to the requested columns, sorted, and trimmed, exactly as in any other query.

The important mental model is that ANY/ALL are evaluated logically as a set of per-row comparisons, but a good query planner never actually performs an O(outer rows × inner rows) nested loop for the common cases above — it reduces the subquery to the one number (or lookup structure) the comparison actually needs, then reuses it for every outer row. That is also why these operators only cost roughly one extra pass over the subquery’s table, not one pass per outer row.

Common Mistakes

Mistake 1: Assuming ANY means “greater than everything”

It is easy to misread salary > ANY (subquery) as “greater than all of them.” It actually means the opposite bar — greater than at least one of them, the weakest possible condition among the group. Compare Example 1 (ANY, four matches) with Example 2 (ALL, two matches) above: if you want “beats everyone,” you need ALL, not ANY.

Mistake 2: Writing ANY/ALL directly against SQLite

Running the standard syntax unmodified against SQLite fails outright:

SELECT name
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales');

SQLite raises a syntax error at ALL, because it never implemented the ANY/ALL/SOME quantifiers. The fix is the MAX()/MIN() rewrite from the table earlier in this lesson:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Alice', 'Engineering', 95000),
  (2, 'Frank', 'Sales', 60000),
  (3, 'Grace', 'Sales', 70000),
  (4, 'Henry', 'Support', 50000);

SELECT name
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department = 'Sales');

Result: one row, Alice — the Sales maximum is 70000, and only Alice’s 95000 exceeds it.

Mistake 3: NOT IN / <> ALL silently returning zero rows because of a NULL

This is the single most common ALL-related bug, and it hits NOT IN just as hard because <> ALL and NOT IN are the same operation. If the subquery’s column contains even one NULL, the whole predicate can silently stop matching anything:

CREATE TABLE employees (id INTEGER, name TEXT);
INSERT INTO employees VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');

CREATE TABLE flagged_ids (id INTEGER);
INSERT INTO flagged_ids VALUES (1), (NULL);

SELECT * FROM employees WHERE id NOT IN (SELECT id FROM flagged_ids);

Result: zero rows — not the two rows (Bob, Carol) you would expect. SQL uses three-valued logic: for Bob, the check becomes 2 = 1 OR 2 = NULL, which evaluates to NULL (unknown) rather than false, and WHERE only keeps rows where the condition is definitely true. Because ALL/NOT IN requires the comparison to hold against every row including the NULL one, a single unknown poisons the entire result. The safe fix is to filter out NULLs in the subquery or use NOT EXISTS instead, which does not have this problem.

Best Practices

Prefer IN over = ANY and NOT IN/NOT EXISTS over <> ALL — they mean the same thing, read more naturally, and work on every SQL engine including SQLite.
For > ANY, < ANY, > ALL, and < ALL, rewrite as a comparison against MIN() or MAX() — it is portable, usually just as fast or faster, and easier to read at a glance.
Always double-check whether the subquery column can contain NULL before using NOT IN or <> ALL; if it can, filter the NULLs out (WHERE col IS NOT NULL) or switch to NOT EXISTS.
Remember that ALL against an empty subquery is vacuously true and ANY against an empty subquery is always false — test the zero-row case explicitly if it is reachable in your data.
Index the column the subquery filters or groups on; it is what the aggregate/semi-join rewrite actually scans, and a missing index there defeats the whole optimization.
Give the subquery a clear, single-purpose filter — a subquery that tries to answer two questions at once makes the outer ANY/ALL comparison hard to reason about later.

Practice Exercises

Using a table of your own design (id, name, department, salary), write a SQLite-compatible query that returns employees earning more than at least one Engineering employee. Hint: rewrite > ANY using MIN().
Using the same table, write a query that returns employees earning more than every Engineering employee. Hint: rewrite > ALL using MAX().
A colleague writes WHERE customer_id NOT IN (SELECT customer_id FROM orders) to find customers with no orders, but the query returns nothing even though you can see unmatched customers in the data. What is the most likely cause, and how would you rewrite the query to fix it?

Summary

ANY (a.k.a. SOME) is true if the comparison holds for at least one row the subquery returns; ALL is true only if it holds for every row.
= ANY behaves exactly like IN, and <> ALL behaves exactly like NOT IN.
> ANY/< ANY and > ALL/< ALL can always be rewritten as a comparison against the subquery’s MIN() or MAX().
SQLite does not implement the ANY/ALL/SOME keywords — use the IN/NOT IN/MIN/MAX rewrites shown in this lesson, which also work on every other major database.
An empty subquery makes ALL vacuously true and ANY always false.
Watch for NULLs in the subquery when using NOT IN/<> ALL — a single NULL can silently make the whole predicate match nothing.

SQL ANY and ALL

Overview: How ANY and ALL Work

Syntax

Examples

Example 1: ANY — salary greater than at least one Marketing employee

Example 2: ALL — salary greater than every Marketing employee

Example 3: = ANY — the same thing as IN

How It Works Step by Step (Under the Hood)

Common Mistakes

Mistake 1: Assuming ANY means “greater than everything”

Mistake 2: Writing ANY/ALL directly against SQLite

Mistake 3: NOT IN / <> ALL silently returning zero rows because of a NULL

Best Practices

Practice Exercises

Summary

Table of Contents