SQL - ANY, ALL Operators

SQL - The ANY and ALL Operators

The SQL ANY and ALL operators are used with a comparison operator (=, >, <, etc.) to compare a single value to a set of values returned by a subquery. While part of the SQL standard, they are less common in modern practice, but understanding them provides a deeper insight into SQL’s expressive power.

The Core Idea:

• > ANY (set) means > MIN(set) (greater than at least one value).
• > ALL (set) means > MAX(set) (greater than every value).
• < ANY (set) means < MAX(set) (less than at least one value).
• < ALL (set) means < MIN(set) (less than every value).
• = ANY (set) is identical to IN (set).
• <> ALL (set) is identical to NOT IN (set).

Let’s use a products table for our examples:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Mouse', 'Electronics', 25.00),
(3, 'T-Shirt', 'Apparel', 20.00),
(4, 'Jeans', 'Apparel', 75.00),
(5, 'Coffee Maker', 'Home Goods', 100.00);

The SQL ANY Operator

The ANY operator returns TRUE if the comparison is true for at least one of the values returned by the subquery.

Example: Find Products Pricier Than Any Apparel Item

We want to find products that are more expensive than the cheapest apparel item. The subquery will return the prices of all apparel items (20.00, 75.00). The condition price > ANY (...) will be true for any product with a price greater than 20.00.

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

This is logically equivalent to WHERE price > (SELECT MIN(price) FROM products WHERE category = 'Apparel').

name	price
Laptop	1200.00
Mouse	25.00
Jeans	75.00
Coffee Maker	100.00

Using ANY with = (is equivalent to IN)

The expression = ANY is a synonym for the more commonly used IN operator.

-- Find products with a price that matches any apparel price.
SELECT name, price FROM products
WHERE price = ANY (SELECT price FROM products WHERE category = 'Apparel');

-- This is identical to:
SELECT name, price FROM products
WHERE price IN (20.00, 75.00);

The SQL ALL Operator

The ALL operator returns TRUE if the comparison is true for all of the values returned by the subquery.

Example: Find Products Pricier Than All Apparel Items

Now, we want to find products that are more expensive than the most expensive apparel item. The condition price > ALL (...) will only be true for products with a price greater than 75.00.

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

This is logically equivalent to WHERE price > (SELECT MAX(price) FROM products WHERE category = 'Apparel').

name	price
Laptop	1200.00
Coffee Maker	100.00

Practical Alternatives and Modern Usage

While ANY and ALL are functionally correct, modern SQL developers often prefer more explicit and arguably more readable alternatives.

ANY/ALL Expression	More Common & Readable Alternative
col = ANY (subquery)	col IN (subquery)
col <> ANY (subquery)	NOT (col = ALL (subquery)) (no direct simple alternative)
col <> ALL (subquery)	col NOT IN (subquery)
col > ANY (subquery)	col > (SELECT MIN(...) FROM ...)
col > ALL (subquery)	col > (SELECT MAX(...) FROM ...)

Using IN, NOT IN, EXISTS, or subqueries with aggregate functions (MIN, MAX, AVG) often makes the query’s intent clearer to other developers. However, understanding ANY and ALL is beneficial for reading older code or for complex scenarios where they might provide a more concise solution.