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.

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.

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