MySQL ANY and ALL Operators

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, WHERE and HAVING statements

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:

And a selection from the “OrderDetails” table:

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:

1. CREATE TABLE table1 (  
2.     num_value INT  
3. );   
4. INSERT INTO table1 (num_value)   
5. VALUES(10), (20), (25);  
6.   
7. CREATE TABLE table2 (  
8.     num_val int  
9. );   
10. INSERT INTO table2 (num_val)  
11. 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:

1. SELECT num_value FROM table1   
2. 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:

1. SELECT colm1 FROM table1 WHERE colm1 = ANY (SELECT colm1 FROM table2);  
2. 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:

1. SELECT colm1 FROM table1 WHERE colm1 <>ANY (SELECT colm1 FROM table2);  
2. 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&gt; 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&lt; 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 &lt;&gt; ALL (select Cust_Id from tickets);</code>

Output: