The SQL WHERE ANY and ALL syntax

The general ANY syntax is:

1. SELECT column–names
2. FROM table–name
3. WHERE column–name operator ANY
4. (SELECT column–name
5. FROM table–name
6. WHERE condition)

The general ALL syntax is:

1. SELECT column–names
2. FROM table–name
3. WHERE column–name operator ALL
4. (SELECT column–name
5. FROM table–name
6. WHERE condition)

SQL ANY Example

Problem: Which products were sold by the unit (i.e. quantity = 1)

1. SELECT ProductName
2. FROM Product
3. WHERE Id = ANY
4. (SELECT ProductId
5. FROM OrderItem
6. WHERE Quantity = 1)

Results: 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

SQL ALL Example

Problem: List customers who placed orders that are
larger than the average of each customer order

1. SELECT DISTINCT FirstName + ‘ ‘ + LastName as CustomerName
2. FROM Customer, [Order]
3. WHERE Customer.Id = [Order].CustomerId
4. AND TotalAmount > ALL
5. (SELECT AVG(TotalAmount)
6. FROM [Order]
7. GROUP BY CustomerId)

Results: 22 records