SQL ANY and ALL Operators

Best SQL ANY and ALL Operators in 2023

by

The SQL ANY and ALL Operators

The sql ANY and ALL operators are used in combination with subqueries to compare a single value with a set of values in SQL.

Here, we will learn the ANY and ALL operators with examples.

Examples of SQL ANY and ALL Operators

  1. SQL ANY Operator:
    The ANY operator is used to compare a value with a set of values in a subquery and returns true if the comparison is true for at least one value in the set.

Example 1: ANY with Comparison Operators
Let’s say you have a table called Products with a Price column, and you want to find products with a price greater than any product in a certain category.

SQL> SELECT ProductName, Price
FROM Products
WHERE Price > ANY (SELECT Price FROM Products WHERE Category = ‘Electronics’);

The subquery (SELECT Price FROM Products WHERE Category = ‘Electronics’) retrieves the prices of all products in the ‘Electronics’ category.


Price > ANY… compares the price of each product with the set of prices from the subquery and returns products with prices greater than any product in the ‘Electronics’ category.

  1. SQL ALL Operator:

The ALL operator is used to compare a value with a set of values in a subquery and returns true if the comparison is true for all values in the set.

Example 2: ALL with Comparison Operators
Let’s say you want to find products with a price greater than all products in a certain category. You can use the ALL operator like this:

SQL>SELECT ProductName, Price
FROM Products
WHERE Price > ALL (SELECT Price FROM Products WHERE Category = ‘Books’);

The subquery (SELECT Price FROM Products WHERE Category = ‘Books’) retrieves the prices of all products in the ‘Books’ category.


Price > ALL… compares the price of each product with the set of prices from the subquery and returns products with prices greater than all products in
the ‘Books’ category.

  1. ANY and ALL with Other Operators:
    You can use ANY and ALL with various comparison operators, such as =, <, >, <=, and >=, to perform more complex comparisons.

Example 3: Using ANY and ALL with Other Comparison Operators

Let’s say you have a table called “Orders” with an “OrderAmount” column, and you want to find orders with an amount greater than or equal to the largest
order amount in a specific month. You can use ANY or ALL as needed:

—–Using ANY Operator
SQL> SELECT OrderID, OrderAmount
FROM Orders
WHERE OrderAmount >= ANY (SELECT MAX(OrderAmount) FROM Orders WHERE MONTH(OrderDate) = 8);

—–Using ALL Operator
SQL> SELECT OrderID, OrderAmount
FROM Orders
WHERE OrderAmount >= ALL (SELECT MAX(OrderAmount) FROM Orders WHERE MONTH(OrderDate) = 8);

The subquery (SELECT MAX(OrderAmount) FROM Orders WHERE MONTH(OrderDate) = 8) finds the maximum order amount for the specified month.


OrderAmount >= ANY… and OrderAmount >= ALL… compare the order amount of each order with the maximum order amount from the subquery.

You can filter results based on sophisticated constraints involving multiple values by using the ANY and ALL operators to compare sets of values in subqueries.

Leave a Comment