SQL WHERE ANY, ALL Clause ~ niit post

• ANY and ALL keywords are used with a WHERE or HAVING clause.
• 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.

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)

PRODUCT

Id

ProductName

SupplierId

UnitPrice

Package

IsDiscontinued

ORDERITEM

Id

OrderId

ProductId

UnitPrice

Quantity

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

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

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 

CustomerName

Art Braunschweiger

Christina Berglund

Elizabeth Lincoln

Frédérique Citeaux

Georg Pipps

Horst Kloss

Howard Snyder

...