Skip to content
Tutorials Center
My learning

Topics

Lesson 46 of 125 ยท Subqueries and Set Operations

ANY, SOME and ALL with Subqueries

Learn SQL ANY, SOME and ALL: compare a value with every row of a subquery, map them to IN, MIN and MAX, and avoid the empty-set and NULL traps.

A scalar subquery returns one value, so you can write salary > (SELECT ...). But what if you want to compare against a list of values: "earns more than everyone in Support" or "earns more than at least one person in Design"? A plain > can't take a list, and IN only tests equality.

SQL's answer is the quantified comparison: put ANY, SOME or ALL between the comparison operator and a subquery that returns one column. This lesson explains what each quantifier means, how the standard operators map onto familiar tools like IN, MAX and EXISTS, and the two traps (empty results and NULLs) that make ALL the trickiest keyword in the family.

PostgreSQL, MySQL, SQL Server and Oracle all support ANY, SOME and ALL with a subquery. SQLite does not, and neither does the SQL runner on this page. Every runnable example below therefore uses the equivalent rewrite, and the quantified version is shown as a read-only block so you can compare the two forms.

The idea: compare against a set

A quantified comparison has this shape:

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

The subquery must return exactly one column, any number of rows. The database compares your expression with each returned value using the operator, and then combines the answers:

  • ANY (and its synonym SOME): the condition is true if the comparison is true for at least one value.
  • ALL: the condition is true only if the comparison is true for every value.

Think of ANY as a chain of ORs and ALL as a chain of ANDs. If the subquery returns 80000, 90000 and 100000, then x > ANY (...) means x > 80000 OR x > 90000 OR x > 100000, and x > ALL (...) means x > 80000 AND x > 90000 AND x > 100000.

ANY: "at least one"

Find the employees who earn more than at least one person in Design (department 2). In a database with quantified comparisons you write:

SQL
-- PostgreSQL, MySQL, SQL Server, Oracle (read-only here)
SELECT first_name, last_name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary FROM employees WHERE department_id = 2
);

"Greater than at least one value" is the same as "greater than the smallest value", so > ANY is equivalent to comparing with MIN. This version runs everywhere, including here:

SQL > ANY is the same as > MIN ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The lowest Design salary is 56,000, so nearly everyone qualifies. The five lowest earners above it are listed. The same reasoning gives the whole family:

Comparison Equivalent with an aggregate
x > ANY (s) x > (SELECT MIN(...))
x < ANY (s) x < (SELECT MAX(...))
x > ALL (s) x > (SELECT MAX(...))
x < ALL (s) x < (SELECT MIN(...))

The aggregate versions are valid only when the subquery returns at least one non-NULL row. The empty-set section below shows exactly where they diverge.

ALL: "every one"

Who earns more than every Support employee? In quantified form:

SQL
SELECT first_name, last_name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department_id = 4
);

Beating every value means beating the largest, so the portable rewrite compares with MAX:

SQL > ALL is the same as > MAX ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The highest Support salary is 146,000 (the head of Support), so only the people above that line show up.

= ANY is IN, <> ALL is NOT IN

Two special cases need no new thinking, because you already know them:

  • x = ANY (subquery) is exactly x IN (subquery).
  • x <> ALL (subquery) is exactly x NOT IN (subquery).

IN is simply the friendlier spelling. That also means <> ALL inherits the famous NULL trap of NOT IN: if the subquery returns even one NULL, the comparison with that row is "unknown", so <> ALL never evaluates to true and the query returns no rows. Prefer NOT EXISTS when the column can contain NULL.

Be careful with the other combinations: x <> ANY (s) does not mean "not in the list". It means "differs from at least one value", which is true for almost every row when the list has two or more different values. People often write <> ANY when they mean <> ALL.

Cases that aggregates can't express

MIN and MAX cover > and <. Where ANY and ALL really help is when the comparison isn't a simple order, or when the subquery is correlated and you don't want to compute an aggregate per row. The portable fallback is EXISTS or NOT EXISTS:

Quantified form Portable form
x op ANY (SELECT y FROM t WHERE ...) EXISTS (SELECT 1 FROM t WHERE ... AND x op y)
x op ALL (SELECT y FROM t WHERE ...) NOT EXISTS (SELECT 1 FROM t WHERE ... AND NOT (x op y))

For example, "employees paid more than everyone who works in their own department who was hired later" is awkward with MAX, but easy with NOT EXISTS:

SQL ALL as NOT EXISTS ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Each row returned earns more than every colleague in Support who was hired after them. The newest hire has nobody after them, so they qualify automatically, which brings us to the first trap.

Trap 1: ALL over an empty set is true

What happens when the subquery returns no rows at all? The two quantifiers behave oppositely:

  • x > ANY (empty) is false: there is no value you beat.
  • x > ALL (empty) is true: you beat every value in the list, because there are none to fail.

This is vacuous truth, the same logic as "every unicorn in this room is purple". The aggregate rewrite behaves differently: MAX of no rows is NULL, so x > NULL is unknown and no rows are returned. See it for yourself, using a department that doesn't exist:

SQL Empty set: ALL versus MAX ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first count is every employee in the table; the second is zero. If your logic depends on how an empty comparison group behaves, the safest habit is to write the case you mean explicitly with NOT EXISTS and test it with an empty group.

Trap 2: NULL values in the list

NULL is not a number, so 5 > NULL is neither true nor false but unknown. For ANY, an unknown row just doesn't help: one true row is enough. For ALL, a single unknown spoils the result, because "true for every row" can no longer be established. The comparison becomes unknown, and WHERE discards unknown rows.

SQL
-- Returns no rows if ANY salary in department 4 is NULL (read-only)
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 4);

The cure is the same as for NOT IN: filter the subquery with WHERE salary IS NOT NULL, or use the MAX rewrite, since aggregates skip NULLs.

When to use which

  • Use IN / NOT EXISTS for membership; they're clearer and more portable.
  • Use MAX / MIN for simple "bigger than all / smaller than any" questions. They're short and index-friendly. Just handle empty and all-NULL groups on purpose.
  • Use ANY / ALL when your database supports them and the comparison reads naturally, such as price > ALL (SELECT price FROM competitors). They also shine with row constructors and array operands, for example PostgreSQL's x = ANY (ARRAY[1, 2, 3]) for passing a list of ids as one parameter.

Optimizers usually turn simple quantified comparisons into the same plan as the aggregate or EXISTS form, so choose on clarity. If you want to see what your database really did, read the query plan.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which condition is equivalent to x IN (subquery)?

    = ANY is true when x equals at least one returned value, which is exactly what IN means.
  2. 2. salary > ALL (SELECT salary FROM ...) is equivalent to comparing with which aggregate?

    To be greater than every value you must beat the largest one, so the rewrite compares with MAX (as long as the subquery returns at least one non-NULL row).
  3. 3. What does x > ALL (subquery) return when the subquery returns no rows?

    ALL over an empty set is vacuously true, because there is no value that x fails to beat. The MAX rewrite would give NULL instead.
  4. 4. A subquery used with <> ALL returns a NULL among its values. What is the effect?

    <> ALL is the same as NOT IN. A NULL makes the comparison unknown for every outer row, so the WHERE clause keeps nothing.
  5. 5. Which database in this list does not support ANY and ALL with a subquery?

    SQLite has no quantified comparisons, so queries are rewritten with MIN, MAX, IN or EXISTS.

Summary

  • ANY (alias SOME) is true if the comparison holds for at least one subquery value; ALL needs it to hold for every value.
  • = ANY is IN, and <> ALL is NOT IN; <> ANY is a different, rarely wanted condition.
  • > ANY and < ALL reduce to MIN; < ANY and > ALL reduce to MAX, as long as the subquery has non-NULL rows.
  • ALL over an empty set is true, and a NULL in the list makes ALL fail, so guard with NOT EXISTS or IS NOT NULL.
  • SQLite lacks quantified comparisons; the MIN/MAX/EXISTS rewrites work everywhere.

Next: Common table expressions, a cleaner way to name a subquery you want to reuse.