SQL Any,All
The SQL ANY and ALL Operators
You can compare a single column value with a range of additional values using the ANY and ALL operators.
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 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);
The operator needs to be one of the following: =, <>,!=, >, >=, \, 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, WHERE and HAVING 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);
The operator needs to be one of the following: =, <>,!=, >, >=, \, 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 |
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
All of the product names are listed in the SQL query that follows:
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);SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
