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);