SQL ANY and ALL

ANY and ALL compare a value with the result of a subquery. They are useful when a condition depends on a set of values instead of one fixed value.

Syntax

SELECT column_name
FROM table_name
WHERE column_name operator ANY (subquery);

SELECT column_name
FROM table_name
WHERE column_name operator ALL (subquery);

ANY is true when the comparison matches at least one value from the subquery. ALL is true only when the comparison matches every value from the subquery.

Example with ANY

Find products that are more expensive than at least one product in category 2:

SELECT product_name, price
FROM products
WHERE price > ANY (
    SELECT price
    FROM products
    WHERE category_id = 2
);

Example with ALL

Find products that are more expensive than every product in category 2:

SELECT product_name, price
FROM products
WHERE price > ALL (
    SELECT price
    FROM products
    WHERE category_id = 2
);

The second query is stricter. ANY asks for one match. ALL asks the database to be impressed by the entire list.

Common mistakes

• Using ANY when the query really needs the strict behavior of ALL.
• Forgetting that an empty subquery can change the result in ways that are not obvious at first glance.
• Writing a subquery that returns the wrong column.

Related topics

Review SQL EXISTS, SQL IN, SQL Operators and the SQL Formatter.