Using the ALL Keyword

The ALL keyword specifies that the search condition is TRUE if the comparison is TRUE for every value that the subquery returns. If the subquery returns no value, the condition is TRUE.

In the following example, the first condition tests whether each total_price is greater than the total price of every item in order number 1023. The second condition uses the MAX aggregate function to produce the same results.

total_price > ALL (SELECT total_price FROM items
                WHERE order_num = 1023)

total_price > (SELECT MAX(total_price) FROM items
                WHERE order_num = 1023)

Using the NOT keyword with an ALL subquery tests whether an expression is not TRUE for at least one element that the subquery returns. For example, the following condition is TRUE when the expression total_price is not greater than all the selected values. That is, it is TRUE when total_price is not greater than the highest total price in order number 1023.

NOT total_price > ALL (SELECT total_price FROM items
                  WHERE order_num = 1023)