SQLANY and ALL Operators
The SQL ANY and ALL Operators
TheANY andALL operators allow you to perform a comparison between a single column value and a range of other values.
The SQL ANY Operator
TheANY 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
FROMtable_name
WHEREcolumn_name operator ANY
(SELECTcolumn_name
FROMtable_name
WHEREcondition);
Note: Theoperator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
The SQL ALL Operator
TheALL 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
FROMtable_name
WHEREcondition;
ALL Syntax With WHERE or HAVING
FROMtable_name
WHEREcolumn_name operator ALL
(SELECTcolumn_name
FROMtable_name
WHEREcondition);
Note: Theoperator 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
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
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
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
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
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity > 1000);
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
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
