ALL, ANY, SOME Subqueries

The following examples return the order number of all orders that contain an item whose total price is greater than the total price of every item in order number 1023. The first SELECT uses the ALL subquery, and the second SELECT produces the same result by using the MAX aggregate function.

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

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

The following SELECT statements return the order number of all orders that contain an item whose total price is greater than the total price of at least one of the items in order number 1023. The first SELECT statement uses the ANY keyword, and the second SELECT statement uses the MIN aggregate function:

SELECT DISTINCT order_num FROM items
   WHERE total_price > ANY (SELECT total_price FROM items
        WHERE order_num = 1023);

SELECT DISTINCT order_num FROM items
   WHERE total_price > (SELECT MIN(total_price) FROM items
      WHERE order_num = 1023);

You can omit the keywords ANY, ALL, or SOME in a subquery if the subquery returns exactly one value. If you omit ANY, ALL, or SOME, and the subquery returns more than one value, you receive an error. The subquery in the next example returns only one row, because it uses an aggregate function:

SELECT order_num FROM items 
   WHERE stock_num = 9 AND quantity =
      (SELECT MAX(quantity) FROM items WHERE stock_num = 9);