Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Subqueries!

For example, consider the problem of listing all books with prices equal to the minimum book price. Using joins, you'd do the job in two steps:

1.	Find the minimum price. SQL select min (price) from titles min(titles.price) ================= 12.99
2.	Get the names of all books selling for this price. SQL select title, price from titles where price = 12.99 title price ===================================== ====== You Can Combat Computer Stress! 12.99 The Gourmet Microwave 12.99 [2 rows]

With a subquery, you need only one statement:

SQL

select title, price
from titles
where price =
						   ( select min (price)
						   from titles )
					

The ability to calculate an aggregate value on the fly and feed it back to the outer query for comparison is a subquery ...