SQL ANY and ALL Operators
The SQL 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 SQL 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 SQL 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 <=).
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
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
Try it Yourself »
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
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity > 99);
Try it Yourself »
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
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity > 1000);
Try it Yourself »
SQL ALL Examples
The following SQL statement lists ALL the product names:
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
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
Try it Yourself »
