SQL Any and All

The ANY and ALL operators are logical comparison operators used alongside standard comparison operators (=, >, <, >=, <=, <>) to compare a scalar value against a single-column set of values returned by a subquery.

The ANY Operator

The ANY operator returns TRUE if the comparison condition is satisfied for at least one value in the subquery result set.

If the subquery returns no rows, ANY evaluates to FALSE.

Syntax

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

Logical Equivalencies for ANY

• = ANY is equivalent to the IN operator.
• > ANY means greater than the minimum value in the subquery set.
• < ANY means less than the maximum value in the subquery set.

Example: Using ANY

Suppose we have a Products table and an OrderDetails table. To find all products that have been ordered in quantities greater than 10 units in any single order line item:

SELECT ProductName, Price
FROM Products
WHERE ProductID = ANY (
    SELECT ProductID
    FROM OrderDetails
    WHERE Quantity > 10
);

The ALL Operator

The ALL operator returns TRUE only if the comparison condition is satisfied for every single value in the subquery result set.

If the subquery returns no rows, ALL automatically evaluates to TRUE.

Syntax

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

Logical Equivalencies for ALL

• > ALL means greater than the maximum value in the subquery set.
• < ALL means less than the minimum value in the subquery set.
• <> ALL is equivalent to the NOT IN operator (provided no NULL values are present).

Example: Using ALL

To find all products whose unit price is higher than the average product price of all individual product categories:

SELECT ProductName, Price
FROM Products
WHERE Price > ALL (
    SELECT AVG(Price)
    FROM Products
    GROUP BY CategoryID
);

Visualizing ANY vs. ALL

Assume the inner subquery returns the set of values: {10, 20, 30}.

Candidate Value: 15

15 > ANY (10, 20, 30)  ---> TRUE  (15 > 10 is satisfied)
15 > ALL (10, 20, 30)  ---> FALSE (15 is not greater than 20 or 30)

Candidate Value: 35

35 > ANY (10, 20, 30)  ---> TRUE  (35 > 10, 20, 30)
35 > ALL (10, 20, 30)  ---> TRUE  (35 is greater than all values)

Comparison Summary Table

Pitfall: NULL Values with ALL

Similar to NOT IN, if the subquery returns even a single NULL value, comparison operators paired with ALL (such as > ALL or <> ALL) will return UNKNOWN for any values that fail to evaluate to FALSE.

Example of NULL Risk:

-- If subquery returns {10, 20, NULL}
SELECT CustomerName
FROM Customers
WHERE Age > ALL (SELECT Age FROM RestrictedUsers);

In this scenario, if Age is 25, 25 > NULL resolves to UNKNOWN. As a result, the overall condition yields UNKNOWN and discards the row. Always ensure subqueries used with ALL filter out NULL values using WHERE column IS NOT NULL.