SQL ANY, ALL

What are SQL ANY and ALL operators?

ANY returns TRUE if the comparison is true for at least one value in a subquery result set, while ALL returns TRUE only if the comparison is true for every value in that set.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL ANY, ALL: PostgreSQL, MySQL (8.0+), SQL Server, Oracle, SQLite (3.35+), Snowflake, Redshift, MariaDB, IBM Db2

SQL ANY, ALL Full Explanation

ANY and ALL are comparison modifiers used with subqueries. They let you compare a single scalar expression to a set of values returned by a subquery.- ANY (or its synonym SOME) evaluates the comparison operator against every value produced by the subquery and returns TRUE if the predicate is satisfied for at least one row. If the subquery returns no rows, ANY yields FALSE.- ALL requires the predicate to be TRUE for every row produced by the subquery. If the subquery returns no rows, ALL yields TRUE.Both operators expect the subquery to return exactly one column, and the data type must be comparable to the outer expression. They can be used with =, <>, !=, >, <, >=, <=. For NOT logical negation, you can wrap the entire predicate in NOT.NULL handling follows three-valued logic: if any comparison result is NULL, the overall evaluation follows SQL's normal NULL rules. For example, expr = ANY(subquery) ignores NULLs in the subquery during equality tests but not during inequality tests, depending on the DBMS.Performance tips: Use EXISTS/NOT EXISTS when only the presence of rows matters. Ensure the subquery has appropriate indexes to avoid scanning large tables.

SQL ANY, ALL Syntax

expression comparison_operator ANY (subquery);
expression comparison_operator SOME (subquery); -- SOME is an alias for ANY
expression comparison_operator ALL (subquery);

SQL ANY, ALL Parameters

expression (scalar) - Value being compared to the subquery results
comparison_operator (operator) - One of =, <>, !=, >, =, <=
subquery (SELECT statement) - Must return exactly one column whose data type is comparable to expression

Example Queries Using SQL ANY, ALL

-- Price lower than at least one competitor price
SELECT product_id, price
FROM products p
WHERE p.price < ANY (
    SELECT cp.price
    FROM competitor_prices cp
    WHERE cp.competitor_id = 5
);

-- Employees earning more than everyone in their department
SELECT employee_id, salary
FROM employees e
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = e.department_id
);

-- Find orders whose amount equals all installment payments (same for each installment)
SELECT order_id
FROM orders o
WHERE o.amount = ALL (
    SELECT payment_amount
    FROM installments i
    WHERE i.order_id = o.order_id
);

Expected Output Using SQL ANY, ALL

Query 1 returns products where the price is less than at least one competitor price for competitor 5
Query 2 returns employees whose salary is higher than every coworker in their department
Query 3 returns orders in which all related installment payments equal the order amount

Use Cases with SQL ANY, ALL

Compare a value to a variable set of numbers from another table (e.g., price vs competitor prices).
Identify maximum or minimum records without using MAX/MIN (e.g., salary > ALL to find the top earner).
Validate uniformity (e.g., value = ALL to ensure all related rows share the same value).
Replace IN when you need inequality comparisons (>, <, >=, <=) against a subquery result.

Common Mistakes with SQL ANY, ALL

Forgetting that the subquery must return exactly one column.
Assuming ANY behaves like IN for NULLs; NULL handling differs.
Using = ALL expecting it to behave like IN; = ALL requires the value to match every row, not any row.
Neglecting performance; large unindexed subqueries can be slow.
Confusing NOT IN with <> ALL; NOT IN returns UNKNOWN if NULLs exist, but <> ALL ignores NULLs.

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the difference between SQL ANY and ALL?

ANY (or SOME) succeeds if the predicate is true for at least one subquery value, while ALL requires the predicate to be true for every subquery value.

Is SOME the same as ANY?

Yes. SOME is an ISO-standard synonym for ANY and behaves identically in all major databases.

How do ANY and ALL deal with empty subqueries?

ANY returns FALSE when the subquery returns zero rows. ALL returns TRUE (vacuous truth).

When should I use ANY/ALL instead of IN or EXISTS?

Use ANY/ALL when you need inequality comparisons (<, >, <=, >=) or need to confirm all rows meet a condition. Use EXISTS for pure presence checks and IN for equality checks when NULLs are handled appropriately.