SQL WHERE ANY, ALL Clause

  • 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 definitive guide
for data professionals


Order today!
 See 2 min video



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)




SQL ANY Example



Problem: Which products were sold by the unit (i.e. quantity = 1)
SELECT ProductName
  FROM Product
 WHERE Id = ANY
       (SELECT ProductId 
          FROM OrderItem 
         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
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)


Results: 22 records


CustomerName
Art Braunschweiger
Christina Berglund
Elizabeth Lincoln
Frédérique Citeaux
Georg Pipps
Horst Kloss
Howard Snyder