User's Guide

Part II. Tutorials

Chapter 12. Introduction to Subqueries

Sometimes it is useful to use the results of one statement as part of another statement.

Example 1

For example, suppose that you need a list of order items for products that are low in stock.

You can look up the products for which there are less than 20 items in stock in the product table.

To list all products for which there are less than 20 items in stock:

• Type the following:

       SELECT id, description, quantity
       FROM product
       WHERE quantity < 20
  

id	description	quantity
401	Wool cap	12

This query shows that only wool caps are low in stock.

Example 2

You can list all the order items for wool caps with the following query:

To list all orders for wool caps, most recent first:

• Type the following:

       SELECT *
       FROM sales_order_items
       WHERE prod_id = 401
       ORDER BY ship_date DESC
  

id	line_id	prod_id	quantity	ship_date
2082	1	401	48	1994-07-09
2053	1	401	60	1994-06-30
2125	2	401	36	1994-06-28
2027	1	401	12	1994-06-17
2062	1	401	36	1994-06-17

This two-step process of identifying items low in stock and identifying orders for those items can be combined into a single query using subqueries.