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

sql

### 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.

sql
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

sql

### SELECT ProductName

### FROM Products

### WHERE ProductID = ALL (

### SELECT ProductID

### FROM OrderDetails

### WHERE Quantity = 10
);

The product must appear in every order with quantity

10.

sql
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".