The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
The ANY Operator
The ANY operator:
- returns a boolean value as a result
- returns TRUE if ANY of the subquery values meet the condition
ANY means that the condition will be true if the operation is true for any of the values in the range.
ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
The ALL Operator
The ALL operator:
- returns a boolean value as a result
- returns TRUE if ALL of the subquery values meet the condition
- is used with
SELECT,WHEREandHAVINGstatements
ALL means that the condition will be true only if the operation is true for all values in the range.
ALL Syntax With SELECT
SELECT ALL column_name(s)
FROM table_name
WHERE condition;ALL Syntax With WHERE or HAVING
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
When to use any and all in SQL?
ANY means that the condition will be satisfied if the operation is true for any of the values in the range. ALL means that the condition will be satisfied only if the operation is true for all values in the range.
Demo Database
Below is a selection from the “Products” table in the Northwind sample database:
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
| 2 | Chang | 1 | 1 | 24 – 12 oz bottles | 19 |
| 3 | Aniseed Syrup | 1 | 2 | 12 – 550 ml bottles | 10 |
| 4 | Chef Anton’s Cajun Seasoning | 2 | 2 | 48 – 6 oz jars | 22 |
| 5 | Chef Anton’s Gumbo Mix | 2 | 2 | 36 boxes | 21.35 |
| 6 | Grandma’s Boysenberry Spread | 3 | 2 | 12 – 8 oz jars | 25 |
| 7 | Uncle Bob’s Organic Dried Pears | 3 | 7 | 12 – 1 lb pkgs. | 30 |
| 8 | Northwoods Cranberry Sauce | 3 | 2 | 12 – 12 oz jars | 40 |
| 9 | Mishi Kobe Niku | 4 | 6 | 18 – 500 g pkgs. | 97 |
And a selection from the “OrderDetails” table:
| OrderDetailID | OrderID | ProductID | Quantity |
|---|---|---|---|
| 1 | 10248 | 11 | 12 |
| 2 | 10248 | 42 | 10 |
| 3 | 10248 | 72 | 5 |
| 4 | 10249 | 14 | 9 |
| 5 | 10249 | 51 | 40 |
| 6 | 10250 | 41 | 10 |
| 7 | 10250 | 51 | 35 |
| 8 | 10250 | 65 | 15 |
| 9 | 10251 | 22 | 6 |
| 10 | 10251 | 57 | 15 |
SQL ANY Examples
The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails table has Quantity equal to 10 (this will return TRUE because the Quantity column has some values of 10):
Example
SELECT ProductName<br>FROM Products<br>WHERE ProductID = ANY<br> (SELECT ProductID<br> FROM OrderDetails<br> WHERE Quantity = 10);
The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails table has Quantity larger than 99 (this will return TRUE because the Quantity column has some values larger than 99):
Example
SELECT ProductName<br>FROM Products<br>WHERE ProductID = ANY<br> (SELECT ProductID<br> FROM OrderDetails<br> WHERE Quantity > 99);
The following SQL statement lists the ProductName if it finds ANY records in the OrderDetails table has Quantity larger than 1000 (this will return FALSE because the Quantity column has no values larger than 1000):
Example
SELECT ProductName<br>FROM Products<br>WHERE ProductID = ANY<br> (SELECT ProductID<br> FROM OrderDetails<br> WHERE Quantity > 1000);
SQL ALL Examples
The following SQL statement lists ALL the product names:
Example
SELECT ALL ProductName<br>FROM Products<br>WHERE TRUE;
The following SQL statement lists the ProductName if ALL the records in the OrderDetails table has Quantity equal to 10. This will of course return FALSE because the Quantity column has many different values (not only the value of 10):
Example
SELECT ProductName<br>FROM Products<br>WHERE ProductID = ALL<br> (SELECT ProductID<br> FROM OrderDetails<br> WHERE Quantity = 10);
More Example to Implement Any
Let us create a two table named table1 and table2 and then insert some values into them using the below statements:
- CREATE TABLE table1 (
- num_value INT
- );
- INSERT INTO table1 (num_value)
- VALUES(10), (20), (25);
- CREATE TABLE table2 (
- num_val int
- );
- INSERT INTO table2 (num_val)
- VALUES(20), (7), (10);
After successful execution of the above statement, we can verify it by using the SELECT statement as follows:
Now, we will execute the below statement to understand the use of the ANY operator:
- SELECT num_value FROM table1
- WHERE num_value > ANY (SELECT num_val FROM table2);
This statement returns true and gives the below output because table2 contains (20, 10, and 7) and there is a value 7 in table2 which is less than 10, 20, and 25 of table1.
The IN keyword in MySQL is an alias for = ANY when used with a subquery in the statement. Hence, the below two statements are identical in MySQL:
- SELECT colm1 FROM table1 WHERE colm1 = ANY (SELECT colm1 FROM table2);
- SELECT colm1 FROM table1 WHERE colm1 IN (SELECT colm1 FROM table2);
But we cannot say that IN and = ANY were synonyms when we used it with an expression list. It is because IN can take a list of expressions, but = ANY cannot.
Also, NOT IN cannot be an alias for <> ANY operator, but it can be used for <> ALL. The word SOME in MySQL can be an alias for ANY. Therefore, these two SQL statements are equivalent:
- SELECT colm1 FROM table1 WHERE colm1 <>ANY (SELECT colm1 FROM table2);
- SELECT colm1 FROM table1 WHERE colm1 <> SOME (SELECT colm1 FROM table2);
More Examples to Implement MySQL ALL
Example #1
Query:
<code>select avg(price) avg_price from product group by id order by avg_price;</code>
Output:
Below is the output of the above query in the output console:
Expression > ALL ( subquery )
The above expression returns boolean value true if the expression is greater than the maximum value returned by the subquery.
Example #2
Query:
<code>select p_name,price from product where price> ALL(select avg(price) avg_price from product group by id) order by price;</code>
Output:
Above output the products whose price is greater than the average price of products for all brands.
scalar_expression < ALL ( subquery )
The above expression returns to TRUE if the expression is smaller than the lowest value returned by the subquery. The following example finds the products whose list price is less than the smallest price in the average price list by brand:
Example #3
Query:
<code>select p_name,price from product where price< ALL(select avg(price) avg_price from product group by id) order by price;</code>
Output:
Above output the products whose price is lesser than the average price of products for all brands. Likewise, we can apply ALL operators with many other comparison operators like equal to (=), greater than or equal (>=), less than or equal to (<=), and not equal (!=).
Example #4
Next, we have taken table customers to explain ALL operations.
Query:
<code>select * from customer;</code>
Output:
Query:
<code>select * from tickets;</code>
Output:
Query:
<code>select Cust_Id from customer where Cust_Id <> ALL (select Cust_Id from tickets);</code>
Output:
