SQL 47 🛢️ Subquery Operators: ANY, SOME, and ALL - KandZ

A subquery can return a single value, a list of values, or a set of rows. The comparison operators—=, >, <, >=, <=, <>—compare one value to one value. When the subquery returns multiple rows, a simple comparison fails. The quantifiers ANY, SOME, and ALL extend comparison operators to work with a set of values. They answer questions like “is this value greater than at least one value in the set?” or “is this value greater than every value in the set?”

This chapter covers ANY, SOME, and ALL in full. You will learn the syntax, how each quantifier changes the meaning of a comparison, the equivalences between quantifiers and aggregate functions, and the null-handling behavior that makes these operators subtle. You will also see the practical patterns where they express a query more directly than a join or an aggregate subquery.

Key point: ANY and SOME are synonyms. > ANY means “greater than at least one value in the set.” > ALL means “greater than every value in the set.” Both are used with a comparison operator and a subquery that returns a single column. The result is a boolean.

Why ANY, SOME, and ALL exist

The multi-value comparison problem. A subquery can return many rows. A simple comparison like salary > (SELECT ...) requires the subquery to return exactly one value. When the subquery returns a set, a different operator is needed. ANY and ALL provide the semantics: compare against at least one value, or compare against every value.

The aggregate equivalence problem. > ALL (subquery) is equivalent to > (SELECT MAX(...) ...). < ANY (subquery) is equivalent to < (SELECT MAX(...) ...). These equivalences are useful for understanding what the quantifiers do, and they sometimes suggest a more readable formulation. But the quantifiers are not merely syntactic sugar—they express the intent directly when the comparison is against a set.

The readability problem. Consider a query that finds employees who earn more than every employee in the Sales department. The aggregate form is WHERE salary > (SELECT MAX(salary) FROM employees WHERE department = 'Sales'). The quantifier form is WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales'). Both are correct. The quantifier form reads as “greater than all Sales salaries,” which matches the question more directly.

The null problem. ANY and ALL have specific null-handling behavior that differs from what many developers expect. If the subquery returns a NULL, the comparison may evaluate to NULL rather than true or false. Understanding this behavior is necessary for writing correct queries.

The standardization problem. The SQL standard defines ANY and SOME as synonyms. SOME exists because it reads more naturally in some contexts (“greater than some value”). Both are supported by PostgreSQL, SQL Server, Oracle, and MySQL. The choice between them is stylistic.

a. Syntax and semantics

The syntax is value comparison_operator QUANTIFIER (subquery). The comparison operator is one of =, >, <, >=, <=, <>. The quantifier is ANY, SOME, or ALL.

-- Greater than at least one value
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Sales')

-- Greater than every value
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales')

The ANY quantifier returns true if the comparison holds for at least one value in the subquery’s result. It is equivalent to a series of OR conditions.

-- These are equivalent
WHERE salary > ANY (SELECT salary FROM sales_employees)
WHERE salary > (SELECT MIN(salary) FROM sales_employees)

The ALL quantifier returns true if the comparison holds for every value in the subquery’s result. It is equivalent to a series of AND conditions.

-- These are equivalent
WHERE salary > ALL (SELECT salary FROM sales_employees)
WHERE salary > (SELECT MAX(salary) FROM sales_employees)

The equivalences are worth memorizing because they clarify what each quantifier does:

Quantifier	Equivalent Aggregate
`> ANY`	`> MIN`
`>= ANY`	`>= MIN`
`< ANY`	`< MAX`
`<= ANY`	`<= MAX`
`= ANY`	`IN`
`<> ANY`	Always true (if any value differs)
`> ALL`	`> MAX`
`>= ALL`	`>= MAX`
`< ALL`	`< MIN`
`<= ALL`	`<= MIN`
`= ALL`	Unusual, requires all values equal
`<> ALL`	`NOT IN`

The = ANY equivalence to IN is particularly useful. WHERE id = ANY (SELECT customer_id FROM orders) is the same as WHERE id IN (SELECT customer_id FROM orders). The <> ALL equivalence to NOT IN is the same, including the null trap.

SOME is a synonym for ANY. The standard defines both, and they are interchangeable. WHERE salary > SOME (SELECT ...) is the same as WHERE salary > ANY (SELECT ...). The choice is stylistic; ANY is more common in practice.

b. ANY and SOME in practice

The ANY quantifier is most useful when the question involves “at least one.” A query that finds employees who earn more than at least one Sales employee:

SELECT employee_id, name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department = 'Sales'
);

This returns every employee whose salary exceeds the minimum salary in Sales. It is equivalent to salary > (SELECT MIN(salary) FROM employees WHERE department = 'Sales').

The = ANY form is the quantifier equivalent of IN:

SELECT customer_id, name
FROM customers
WHERE customer_id = ANY (
    SELECT customer_id
    FROM orders
);

This is the same as customer_id IN (SELECT customer_id FROM orders). The IN form is more common, but = ANY is useful when the comparison is part of a larger expression.

A practical use of ANY is checking whether a value matches any value in a set that is computed dynamically. For example, finding products whose price is greater than any price in a specific category:

SELECT product_id, name, price
FROM products
WHERE price > ANY (
    SELECT price
    FROM products
    WHERE category = 'Electronics'
);

This returns products that are more expensive than the cheapest electronics product.

c. ALL and null behavior

The ALL quantifier is most useful when the question involves “every” or “all.” A query that finds employees who earn more than every Sales employee:

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

This returns every employee whose salary exceeds the maximum salary in Sales. It is equivalent to salary > (SELECT MAX(salary) FROM employees WHERE department = 'Sales').

The null behavior of ALL is subtle. If the subquery returns a NULL, the comparison may evaluate to NULL rather than false. Consider:

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

If the Sales subquery returns [50000, 60000, NULL], the comparison salary > 50000 AND salary > 60000 AND salary > NULL evaluates to NULL for every employee, because salary > NULL is NULL. The result is that no rows are returned, even if the employee’s salary is greater than 60000.

This is the same null trap that affects NOT IN. The ALL quantifier has it too. When the subquery’s column is nullable, the result may not be what is expected. The safe alternatives are to filter out nulls in the subquery:

WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department = 'Sales'
      AND salary IS NOT NULL
);

Or to use the aggregate form with MAX, which ignores nulls:

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

The aggregate form is often clearer and does not have the null trap. The quantifier form is useful when the intent is explicitly “all” and the null behavior is understood.

The <> ALL form is the quantifier equivalent of NOT IN:

SELECT customer_id FROM customers
WHERE customer_id <> ALL (SELECT customer_id FROM orders);

This is the same as NOT IN, including the null trap. If the subquery returns any NULL, the result is empty. Use NOT EXISTS instead when nulls are possible.

Complete Example Session

-- ============================================
-- PART 1: SAMPLE DATA
-- ============================================
CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(50),
    department VARCHAR(20),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
    (1, 'Alice', 'Engineering', 95000),
    (2, 'Bob', 'Engineering', 85000),
    (3, 'Carol', 'Sales', 75000),
    (4, 'Dave', 'Sales', 65000),
    (5, 'Eve', 'Marketing', 70000),
    (6, 'Frank', 'Marketing', 72000);

-- ============================================
-- PART 2: > ANY (GREATER THAN AT LEAST ONE)
-- ============================================
SELECT employee_id, name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE department = 'Sales'
);
-- Result: salaries > 65000 (the min Sales salary)
-- Alice (95000), Bob (85000), Eve (70000), Frank (72000), Carol (75000)

-- ============================================
-- PART 3: EQUIVALENT WITH MIN
-- ============================================
SELECT employee_id, name, salary
FROM employees
WHERE salary > (
    SELECT MIN(salary) FROM employees WHERE department = 'Sales'
);
-- Same result

-- ============================================
-- PART 4: > ALL (GREATER THAN EVERY)
-- ============================================
SELECT employee_id, name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department = 'Sales'
);
-- Result: salaries > 75000 (the max Sales salary)
-- Alice (95000), Bob (85000)

-- ============================================
-- PART 5: EQUIVALENT WITH MAX
-- ============================================
SELECT employee_id, name, salary
FROM employees
WHERE salary > (
    SELECT MAX(salary) FROM employees WHERE department = 'Sales'
);
-- Same result

-- ============================================
-- PART 6: = ANY (EQUIVALENT TO IN)
-- ============================================
SELECT employee_id, name
FROM employees
WHERE department = ANY (
    SELECT department FROM employees WHERE salary > 80000
);
-- Result: Engineering (Alice and Bob have salary > 80000)

-- ============================================
-- PART 7: <> ALL (EQUIVALENT TO NOT IN)
-- ============================================
SELECT employee_id, name
FROM employees
WHERE department <> ALL (
    SELECT department FROM employees WHERE salary > 80000
);
-- Result: Sales, Marketing (departments with no one > 80000)

-- ============================================
-- PART 8: THE NULL TRAP WITH ALL
-- ============================================
INSERT INTO employees VALUES (7, 'Grace', 'Sales', NULL);

SELECT employee_id, name
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department = 'Sales'
);
-- Result: empty! The NULL in Sales makes all comparisons NULL

-- ============================================
-- PART 9: FIX WITH IS NOT NULL
-- ============================================
SELECT employee_id, name
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees
    WHERE department = 'Sales' AND salary IS NOT NULL
);
-- Result: Alice (95000), Bob (85000) — correct

-- ============================================
-- PART 10: FIX WITH MAX
-- ============================================
SELECT employee_id, name
FROM employees
WHERE salary > (
    SELECT MAX(salary) FROM employees WHERE department = 'Sales'
);
-- Result: Alice (95000), Bob (85000) — MAX ignores NULL

The ten parts covered > ANY, the MIN equivalence, > ALL, the MAX equivalence, = ANY as IN, <> ALL as NOT IN, the null trap with ALL, the IS NOT NULL fix, and the MAX fix.

Quick Reference

Quantifier Equivalences

Quantifier Form	Aggregate Equivalent
`> ANY`	`> MIN`
`>= ANY`	`>= MIN`
`< ANY`	`< MAX`
`<= ANY`	`<= MAX`
`= ANY`	`IN`
`<> ANY`	Always true
`> ALL`	`> MAX`
`>= ALL`	`>= MAX`
`< ALL`	`< MIN`
`<= ALL`	`<= MIN`
`= ALL`	All values equal
`<> ALL`	`NOT IN`

ANY vs ALL

Aspect	ANY	ALL
Returns true when	Comparison holds for at least one value	Comparison holds for every value
Equivalent to	`OR` across values	`AND` across values
Aggregate equivalent	`MIN` or `MAX` (depending on operator)	`MAX` or `MIN` (depending on operator)
Null behavior	`NULL` can make result `NULL`	`NULL` can make result `NULL`
Common use	“Greater than at least one”	“Greater than every”

Null Safety

Form	Null-Safe
`> ANY`	Depends
`> ALL`	No (if subquery returns `NULL`)
`= ANY`	Yes
`<> ALL`	No (same as `NOT IN`)
`> MAX`	Yes (aggregates ignore `NULL`)

Syntax Forms

Form	Example
`> ANY`	`WHERE salary > ANY (SELECT ...)`
`> SOME`	`WHERE salary > SOME (SELECT ...)`
`> ALL`	`WHERE salary > ALL (SELECT ...)`
`= ANY`	`WHERE id = ANY (SELECT ...)`
`<> ALL`	`WHERE id <> ALL (SELECT ...)`

Best Practices

✅ Do This:

-- Use ANY for "at least one"
WHERE salary > ANY (SELECT salary FROM sales)                  -- ✅

-- Use ALL for "every"
WHERE salary > ALL (SELECT salary FROM sales)                  -- ✅

-- Use the aggregate form when it is clearer
WHERE salary > (SELECT MAX(salary) FROM sales)                 -- ✅

-- Filter out NULLs when using ALL
WHERE salary > ALL (SELECT salary FROM sales
                    WHERE salary IS NOT NULL)                  -- ✅

-- Use = ANY as an alternative to IN
WHERE id = ANY (SELECT customer_id FROM orders)                -- ✅

❌ Don’t Do This:

-- Don't use ALL with a nullable subquery without filtering
WHERE salary > ALL (SELECT salary FROM sales)                  -- ❌ if NULL

-- Don't use <> ALL instead of NOT EXISTS
WHERE id <> ALL (SELECT customer_id FROM orders)               -- ❌ null trap

-- Don't use ANY when the aggregate form is clearer
WHERE salary > ANY (SELECT salary FROM sales)                  -- ⚠️ use MIN

-- Don't forget the comparison operator
WHERE salary ANY (SELECT salary FROM sales)                    -- ❌ syntax error

-- Don't use ANY or ALL with a multi-column subquery
WHERE (a, b) > ANY (SELECT x, y FROM t)                        -- ❌

Common Pitfalls

Pitfall	Why It Happens	Fix
`ALL` returns no rows	NULL in subquery	Filter with `IS NOT NULL`
`ANY` returns unexpected rows	Misunderstanding `MIN`/`MAX` equivalence	Check the aggregate equivalent
`<> ALL` null trap	Same as `NOT IN`	Use `NOT EXISTS`
Syntax error	Missing comparison operator	Add `=`, `>`, `<`, etc.
Multi-column subquery	`ANY`/`ALL` require single column	Use `EXISTS` with row comparison
Confusing `ANY` and `ALL`	Similar names	ANY = at least one, ALL = every

Real-World Examples

1. Employees Above Minimum Sales Salary

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

2. Employees Above Maximum Sales Salary

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

3. Products Priced Above All Electronics

SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products
                   WHERE category = 'Electronics');

4. Orders With Any High-Value Item

SELECT order_id FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items oi
              WHERE oi.order_id = o.order_id
                AND oi.price > ALL (SELECT price FROM products
                                    WHERE category = 'Budget'));

5. Departments With Any High Earner

SELECT DISTINCT department FROM employees
WHERE salary = ANY (SELECT salary FROM employees
                    WHERE salary > 100000);

6. Customers With No Orders (Null-Safe)

SELECT customer_id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id);

7. Employees Above All Managers

SELECT name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees
                    WHERE is_manager = true);

8. Products Cheaper Than Any Premium Item

SELECT name, price FROM products
WHERE price < ANY (SELECT price FROM products
                   WHERE tier = 'premium');

9. Users Who Match Any Admin

SELECT user_id FROM users
WHERE role = ANY (SELECT role FROM users WHERE is_admin = true);

10. Orders Exceeding All Budget Orders

SELECT order_id, total FROM orders
WHERE total > ALL (SELECT total FROM orders
                   WHERE priority = 'budget');

Visual

ANY vs ALL

┌─────────────────────────────────────────────────────────────┐
│  ANY (At Least One)                                         │
│                                                             │
│  Set: {65000, 75000}                                        │
│                                                             │
│  salary > ANY (set)                                         │
│  → salary > 65000 OR salary > 75000                         │
│  → salary > 65000                                           │
│  → Equivalent to salary > MIN(set)                          │
│                                                             │
│  Result: true if salary exceeds the smallest value          │
│                                                             │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  ALL (Every)                                                │
│                                                             │
│  Set: {65000, 75000}                                        │
│                                                             │
│  salary > ALL (set)                                         │
│  → salary > 65000 AND salary > 75000                        │
│  → salary > 75000                                           │
│  → Equivalent to salary > MAX(set)                          │
│                                                             │
│  Result: true if salary exceeds the largest value           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Quantifier and Aggregate Equivalence

┌─────────────────────────────────────────────────────────────┐
│  ANY                                                        │
│                                                             │
│  > ANY   ≡   > MIN                                          │
│  >= ANY  ≡   >= MIN                                         │
│  < ANY   ≡   < MAX                                          │
│  <= ANY  ≡   <= MAX                                         │
│  = ANY   ≡   IN                                             │
│                                                             │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  ALL                                                        │
│                                                             │
│  > ALL   ≡   > MAX                                          │
│  >= ALL  ≡   >= MAX                                         │
│  < ALL   ≡   < MIN                                          │
│  <= ALL  ≡   <= MIN                                         │
│  <> ALL  ≡   NOT IN                                         │
│                                                             │
└─────────────────────────────────────────────────────────────┘

The ALL Null Trap

┌─────────────────────────────────────────────────────────────┐
│  SUBQUERY RETURNS [50000, 60000, NULL]                      │
│                                                             │
│  salary > ALL (subquery)                                    │
│                                                             │
│  Expands to:                                                │
│    salary > 50000                                           │
│    AND salary > 60000                                       │
│    AND salary > NULL                                        │
│                                                             │
│  salary > NULL evaluates to NULL.                           │
│  TRUE AND TRUE AND NULL = NULL.                             │
│  WHERE treats NULL as false.                                │
│                                                             │
│  Result: no rows returned, even if salary > 60000.          │
│                                                             │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  FIX: FILTER NULLS IN SUBQUERY                              │
│                                                             │
│  salary > ALL (SELECT salary FROM sales                     │
│                WHERE salary IS NOT NULL)                    │
│                                                             │
│  Or use MAX:                                                │
│                                                             │
│  salary > (SELECT MAX(salary) FROM sales)                   │
│                                                             │
│  MAX ignores NULL. No trap.                                 │
│                                                             │
└─────────────────────────────────────────────────────────────┘

ANY vs IN

┌─────────────────────────────────────────────────────────────┐
│  = ANY ≡ IN                                                 │
│                                                             │
│  WHERE id = ANY (SELECT customer_id FROM orders)            │
│                                                             │
│  is the same as                                             │
│                                                             │
│  WHERE id IN (SELECT customer_id FROM orders)               │
│                                                             │
│  Both compare a single value against a set.                 │
│  Both require the subquery to return one column.            │
│  Both have the same behavior with NULLs.                    │
│                                                             │
│  IN is more common.                                         │
│  = ANY is useful when the comparison is part of a           │
│  larger expression or when readability favors it.           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Summary

Item	Value
`ANY`	True if comparison holds for at least one value
`SOME`	Synonym for `ANY`
`ALL`	True if comparison holds for every value
`> ANY`	Equivalent to `> MIN`
`> ALL`	Equivalent to `> MAX`
`= ANY`	Equivalent to `IN`
`<> ALL`	Equivalent to `NOT IN`
Subquery columns	Exactly one
Null behavior	Can return `NULL` and filter out rows
Safe alternative	Aggregate function (`MIN`, `MAX`)

Key takeaways:

ANY means “at least one.” > ANY is true if the value is greater than at least one value in the set. It is equivalent to > MIN.
ALL means “every.” > ALL is true if the value is greater than every value in the set. It is equivalent to > MAX.
SOME is a synonym for ANY. They are interchangeable. ANY is more common.
The aggregate equivalences are useful. > ANY is > MIN. > ALL is > MAX. These equivalences clarify what the quantifiers do and often suggest a clearer formulation.
= ANY is equivalent to IN. <> ALL is equivalent to NOT IN. Both have the same null behavior.
ALL has a null trap. If the subquery returns a NULL, the comparison may evaluate to NULL, and no rows are returned. Filter out nulls in the subquery or use the aggregate form with MAX or MIN, which ignore nulls.
The subquery must return exactly one column. ANY and ALL compare a single value against a set of values. A multi-column subquery is a syntax error.
The aggregate form is often clearer. When the comparison is against the minimum or maximum of a set, writing > (SELECT MAX(...)) is more direct than > ALL (SELECT ...). Use the quantifier form when the intent is explicitly “any” or “all.”

Remember: ANY, SOME, and ALL extend comparison operators to work with sets. ANY (or SOME) checks whether the comparison holds for at least one value. ALL checks whether it holds for every value. Both are used with a comparison operator and a single-column subquery. The aggregate equivalences—> ANY as > MIN, > ALL as > MAX—are useful for understanding and often for writing clearer queries. The null behavior of ALL is a trap: a NULL in the subquery can make the entire predicate NULL and return no rows. Filter nulls or use the aggregate form. When the intent is “any” or “all,” the quantifiers express it directly.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!