Subqueries in the WHERE clause work as part of the row selection process. You use a subquery in the WHERE clause when the criteria you use to select rows depend on the results of another table.
Find the products whose in-stock quantities are less than double the average ordered quantity.
SELECT Name, Description FROM Products WHERE Quantity < 2 * ( SELECT AVG( Quantity ) FROM SalesOrderItems );
This is a two-step query: first, find the average number of items requested per order; and then find which products in stock number less than double that quantity.
The Quantity column of the SalesOrderItems table stores the number of items requested per item type, customer, and order. The subquery is
SELECT AVG( Quantity ) FROM SalesOrderItems;
It returns the average quantity of items in the SalesOrderItems table, which is 25.851413.
The next query returns the names and descriptions of the items whose in-stock quantities are less than twice the previously-extracted value.
SELECT Name, Description FROM Products WHERE Quantity < 2*25.851413;
Using a subquery combines the two steps into a single operation.
A subquery in the WHERE clause is part of a search condition. The chapter Queries: Selecting Data from a Table describes simple search conditions you can use in the WHERE clause.