SQL WHERE ANY and ALL Clauses
How are the ANY and ALL keywords used in SQL?
ANY and ALL keywords are used with WHERE or HAVING.
They operate on subqueries that return multiple values.
ANY returns true if any of the subquery values meet the condition.
And ALL returns true if all of the subquery values meet the condition.
The SQL WHERE ANY and ALL syntax
The general ANY syntax is:
SELECT column-names FROM table-name WHERE column-name operator ANY (SELECT column-name FROM table-name WHERE condition)
The general ALL syntax is:
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 |
SQL ANY Example
Problem: Which products were sold
by the unit, that is, quantity = 1.
by the unit, that is, quantity = 1.
SELECT ProductName FROM Product WHERE Id = ANY (SELECT ProductId FROM OrderItem WHERE Quantity = 1)
Result: 17 records
ProductName |
---|
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 |
SQL ALL Example
Problem: List customers who placed orders that are
larger than the average of each customer order.
larger than the average of each customer order.
SELECT DISTINCT FirstName + ' ' + LastName as CustomerName FROM Customer, [Order] WHERE Customer.Id = [Order].CustomerId AND TotalAmount > ALL (SELECT AVG(TotalAmount) FROM [Order] GROUP BY CustomerId)
Result: 22 records
CustomerName |
---|
Art Braunschweiger |
Christina Berglund |
Elizabeth Lincoln |
Frédérique Citeaux |
Georg Pipps |
Horst Kloss |
Howard Snyder |
![]() |