Single-valued subqueries

You do not need to include the keyword ALL or ANY if you know the subquery can return exactly one value to the outer-level query. A subquery that returns exactly one value can be treated like a function. This kind of subquery often uses an aggregate function because aggregate functions always return single values.

The following query uses the aggregate function MAX in a subquery to find the order_num for orders that include the maximum number of volleyball nets.

Figure 1. Query

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

Figure 2. Query result

  order_num 

       1012

The following query uses the aggregate function MIN in the subquery to select items for which the total price is higher than 10 times the minimum price.

Figure 3. Query

SELECT order_num, stock_num, manu_code, total_price
   FROM items x
   WHERE total_price >
      (SELECT 10 * MIN (total_price)
         FROM items
         WHERE order_num = x.order_num);

Figure 4. Query result

order_num stock_num manu_code  total_price 

     1003         8 ANZ           $840.00
     1018       307 PRC           $500.00
     1018       110 PRC           $236.00
     1018       304 HRO           $280.00