MySQL ANY, ALL
The MySQL ANY and ALL Operators
You can compare a single column value with a range of additional values using the ANY and ALL operators.
The ANY Operator
The operator ANY:
- yields a boolean value as the outcome.
- returns TRUE if ANY value in the subquery satisfies the requirement.
ANY indicates that if the operation is true for any of the values in the range, the condition will also be true.
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 operator for ALL:
- yields a boolean value as the outcome.
- returns TRUE if EVERY value in the subquery satisfies the requirement.
- is applied to WHERE, HAVING, and SELECT statements.
ALL denotes that the operation must be true for all values in the range for the condition to be true.
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 <=).
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
Because the Quantity column contains some values of 10, the SQL statement that follows lists the ProductName if it finds ANY records in the OrderDetails table with Quantity equal to 10:
Example
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
If it discovers that ANY records in the OrderDetails table have a quantity greater than 99, the SQL statement that follows lists the ProductName (this will return TRUE because the Quantity column has some values larger than 99):
Example
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity > 99);
If it discovers that ANY records in the OrderDetails table have a quantity more than 1000, the SQL statement that follows lists the ProductName (this will return FALSE because the Quantity column has no values larger than 1000):
Example
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity > 1000);
SQL ALL Examples
The following SQL statement lists ALL the product names:
Example
SELECT ALL ProductName
FROM Products
WHERE TRUE;
If Quantity equals 10 for EVERY record in the OrderDetails table, then the ProductName is listed in the SQL statement that follows. Naturally, since the Quantity column has a wide range of values (not just the number 10), this will yield FALSE:
Example
SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
