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

What is a subquery?

A relational database stores information about different types of objects in different tables. For example, you should store information particular to products in one table, and information that pertains to sales orders in another. The product table contains the information about the various products. The sales order items table contains information about one customers' orders.

In general, only the simplest questions can be answered using only one table. For example, if the company reorders products when there are fewer than 50 of them in stock, then it is possible to answer the question "Which products are nearly out of stock?" with this query:

SELECT id, name, description, quantity
FROM product 
WHERE quantity < 50

However, if "nearly out of stock" depends on how many items of each type the typical customer orders, the number "50" will have to be replaced by a value obtained from the sales_order_items table.

Structure of the subquery 

A subquery is structured like a regular query, and appears in the main query's WHERE or HAVING clause. In the above example, for instance, you can use a subquery to select the average number of items that a customer orders, and then use that figure in the main query to find products that are nearly out of stock. The following query finds the names and descriptions of the products which number less than double the average number of items of each type that a customer orders.

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

SQL subqueries always appear in the WHERE or HAVING clauses of the main query. In the WHERE clause, they help select the rows from the tables listed in the FROM clause that appear in the query results. In the HAVING clause, they help select the row groups, as specified by the main query's GROUP BY clause, that appear in the query results.