HTML tutorial
CSS3 tutorial
Bootstrap tutorial
JavaScript tutorial
JQuery tutorial
AngularJS tutorial
React tutorial
NodeJS tutorial
PHP tutorial
Python tutorial
Python3 tutorial
Django tutorial
Linux tutorial
Docker tutorial
Ruby tutorial
Java tutorial
C tutorial
C ++ tutorial
Perl tutorial
JSP tutorial
Lua tutorial
Scala tutorial
Go tutorial
ASP.NET tutorial
C # tutorial
The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values
The ANY
and ALL
operators allow you to perform a comparison between a single
column value and a range of other values.
The ANY
operator:
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 ALL
operator:
SELECT
,
WHERE
and HAVING
statementsALL
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 <=).
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 |
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):
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 has Quantity larger than 99 (this will return TRUE because the Quantity column has 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 has Quantity 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 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):
SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);