The ANY and ALL operators enable comparisons between a single column value and a range of other values.
The ANY operator:
ANY means that the condition will evaluate to true if it holds for any of the values within the specified range or set.
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition); |
Note: The operator used with ANY must be a standard comparison operator (=, <>, !=, >, >=, <, or <=). |
The ALL operator:
ALL means that the condition will be true only if it holds true for all values in the specified range or set.
SELECT ALL column_name(s) FROM table_name WHERE condition; |
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition); |
Note: The operator used with ALL must be a standard comparison operator (=, <>, !=, >, >=, <, or <=). |
Here is a portion of 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 |
Here is a portion of 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 |
The following SQL statement lists the ProductName if it finds any records in the OrderDetails table where the Quantity is equal to 10 (this will return TRUE because the Quantity column contains some values of 10):
SELECT ProductName 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 where the Quantity is larger than 99 (this will return TRUE because the Quantity column contains some values larger than 99):
SELECT ProductName 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 where the Quantity is larger than 1000 (this will return FALSE because the Quantity column has no values larger than 1000):
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 1000); |
The following SQL statement lists all the product names:
SELECT ALL ProductName FROM Products WHERE TRUE; |
The following SQL statement lists the ProductName if all records in the OrderDetails table have Quantity equal to 10. This will return FALSE because the Quantity column contains many different values, not just the value of 10.
SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); |