ANY and ALL operators are used with WHERE or HAVING.
ANY and ALL operate on subqueries that return multiple values.
ANY returns true if any of the subquery values meet the condition.
ALL returns true if all of the subquery values meet the condition.
List all products that have sold for over $45.
SELECT ProductName AS 'Product'
FROM Product
WHERE Id = ANY
(SELECT ProductId
FROM OrderItem
WHERE UnitPrice > 45)
| Product |
|---|
| Mishi Kobe Niku |
| Carnarvon Tigers |
| Sir Rodney's Marmalade |
| Rössle Sauerkraut |
| Thüringer Rostbratwurst |
| Côte de Blaye |
| Ipoh Coffee |
| Manjimup Dried Apples |
| Raclette Courdavault |
| Tarte au sucre |
ANY syntax.
SELECT column-names
FROM table-name
WHERE column-name operator ANY
(SELECT column-name
FROM table-name
WHERE condition)
ALL syntax.
SELECT column-names
FROM table-name
WHERE column-name operator ALL
(SELECT column-name
FROM table-name
WHERE condition)
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
| ORDERITEM |
|---|
| Id |
| OrderId |
| ProductId |
| UnitPrice |
| Quantity |
SELECT ProductName AS 'Product'
FROM Product
WHERE Id = ANY
(SELECT ProductId
FROM OrderItem
WHERE Quantity = 1)
| Product |
|---|
| Chef Anton's Cajun Seasoning |
| Grandma's Boysenberry Spread |
| Uncle Bob's Organic Dried Pears |
| Ikura |
| Konbu |
| Tofu |
| Teatime Chocolate Biscuits |
| Sir Rodney's Marmalade |
 |
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
SELECT DISTINCT FirstName + ' ' + LastName AS 'Customer'
FROM Customer
JOIN [Order] ON Customer.Id = [Order].CustomerId
AND TotalAmount > ALL
(SELECT AVG(TotalAmount)
FROM [Order]
GROUP BY CustomerId)
| Customer |
|---|
| Art Braunschweiger |
| Christina Berglund |
| Elizabeth Lincoln |
| Frédérique Citeaux |
| Georg Pipps |
| Horst Kloss |
| Howard Snyder |
|
