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