User's Guide
   PART 1. Working with Databases
     CHAPTER 7. Using Subqueries       

Using Subqueries in the WHERE clause

Subqueries in the WHERE clause work as part of the row selection process. You use a subquery in the where clause when the critieria that you use to select rows depend on the results of another table.

Example 

Find the products whose in-stock quantities are less than double the average ordered quantity.

SELECT name, description 
FROM product 
WHERE quantity <  2 * (
   SELECT avg (quantity) 
   FROM sales_order_items)

This query is executed in two steps: first, find the average number of items requested per order; and then find which products in stock number less than double that quantity.

The query in two steps 

The number of items requested per item type, customer, and order is stored in the quantity column of the sales_order_items table. The subquery is

SELECT avg (quantity) 
FROM sales_order_items

It returns the average quantity of items in the sales_order_items table, which is the number 25.851413.

The next query returns the ID numbers, names, and descriptions of the items whose in-stock quantities are less than twice the previously-extracted value

SELECT name, description 
FROM product
WHERE quantity < 2*25.851413

Using a subquery combines the two steps into a single operation.

Purpose of a subquery in the WHERE clause 

A subquery in the WHERE clause is part of a search condition. The chapter Queries: Selecting Data from a Table describes simple search conditions that can be used in the WHERE clause.