SQL WHERE with ANY and ALL

SQL ANY and ALL Explained

ANY and ALL keywords 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.

Example

#

Problem: List products that have sold for over $45.

copy

SELECT ProductName AS 'Product'
  FROM Product
 WHERE Id = ANY
       (SELECT ProductId 
          FROM OrderItem 
         WHERE UnitPrice > 45)

SELECT ProductName AS 'Product'
  FROM Product
 WHERE Id = ANY
       (SELECT ProductId 
          FROM OrderItem 
         WHERE UnitPrice > 45)

SQL Alias

SQL Exists

Syntax

#

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)

More Examples

#

SQL ANY Subquery

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued

ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity

Problem: List products that were sold by the unit, that is, quantity = 1.

copy

SELECT ProductName AS 'Product'
  FROM Product
 WHERE Id = ANY
       (SELECT ProductId 
          FROM OrderItem 
         WHERE Quantity = 1)

SELECT ProductName AS 'Product'
  FROM Product
 WHERE Id = ANY
       (SELECT ProductId 
          FROM OrderItem 
         WHERE Quantity = 1)

Result: 17 records

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

SQL ALL Subquery

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

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

copy

SELECT DISTINCT FirstName + ' ' + LastName AS 'Customer'
  FROM Customer, [Order]
 WHERE Customer.Id = [Order].CustomerId
   AND TotalAmount > ALL 
       (SELECT AVG(TotalAmount)
          FROM [Order]
         GROUP BY CustomerId)

SELECT DISTINCT FirstName + ' ' + LastName AS 'Customer'
  FROM Customer, [Order]
 WHERE Customer.Id = [Order].CustomerId
   AND TotalAmount > ALL 
       (SELECT AVG(TotalAmount)
          FROM [Order]
         GROUP BY CustomerId)

Result: 22 records