Single-Value Subqueries

A single-value subquery is the simplest and most often used subquery. This subquery retrieves a single value (often computed from an aggregate function). A single value subquery has the following form:

SELECT ... FROM ... WHERE expression rel_oper (SELECT expression FROM ...)

The subquery's SELECT statement must return only one row.

The following example shows the use of a single value subquery in a select statement that retrieves the list of books that cost more than the average cost of a book.

select price, cost, title from book 
    where cost > (select avg(cost) from book);
       PRICE         COST TITLE
     7500.00      6000.00 'The fear that walks by noonday.'
    12500.00      9615.00 A Christmas carol.
    15000.00     11538.00 A tale of two cities,
    12500.00      9615.00 Adventures of Sherlock Holmes.
    15000.00     11538.00 An inland voyage.
       ...
    10000.00      8000.00 Treasure Island.
    10000.00      8000.00 Uncle Tom's cabin.
     9500.00      7600.00 Villette.
    17500.00     13461.00 Walden; or, Life in the woods. By Henry D. Thoreau.
    25000.00     19230.00 Works. 1709

Single-value subqueries can also be used as arguments to stored procedures and functions as well as in a SET statement to assign a value to the result of a single-value subquery. Both of these uses are shown in the example below.

declare avg_price double;
set @avg_price = (select avg(price) from book);
select price, title from book where price > avg_price;
	...
create prcoedure grthan(my_price double)
 	select price, title from book where price > my_price;
call grthan((select avg(price) from book));
	...