ANY and ALL

ANY and ALL

ANY and ALL compare a value against the results of a subquery that returns a single column.

ANY - matches at least one

SELECT ProductName
FROM Products
WHERE ProductID = ANY (
  SELECT ProductID
  FROM OrderDetails
  WHERE Quantity > 50
);

Returns products that appear in any order with more than 50 items.

WHERE Price > ANY (SELECT Price FROM Products WHERE CategoryID = 1)

TRUE if the price is greater than at least one of the category 1 prices.

ALL - matches every one

SELECT ProductName
FROM Products
WHERE ProductID = ALL (
  SELECT ProductID
  FROM OrderDetails
  WHERE Quantity = 10
);

The product must appear in every order with quantity 10.

WHERE Price > ALL (SELECT Price FROM Products WHERE CategoryID = 1)

TRUE only if the price is greater than every category 1 price (i.e. the most expensive).

The difference

TL;DR

• ANY is true if the condition holds for at least one subquery row.
• ALL is true only if it holds for every row.
• The subquery must return a single column.
• "Price > ALL(...)" means "more expensive than everything in the list".