User's Guide

Part II. Tutorials

Chapter 12. Introduction to Subqueries

SQL provides another way to find orders for items low in stock. The following query incorporates a subquery.

Example 1

To list order items for products low in stock:

• Type the following:

       SELECT *
       FROM sales_order_items
       WHERE prod_id IN
       ( SELECT id
       FROM product
       WHERE quantity < 20 )
       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

By using a subquery, the search can be carried out in just one query, instead of using one query to find the list of low-stock products and a second to find orders for those products.

The subquery in the statement is the phrase enclosed in parentheses:

     ( SELECT id
     FROM product
     WHERE quantity < 20 )

The subquery makes a list of all values in the id column in the product table satisfying the WHERE clause search condition.

Example 2

Consider what would happen if an order for ten tank tops were shipped so that the quantity column for tank tops contained the value 18. The query using the subquery, would list all orders for both wool caps and tank tops. On the other hand, the first statement you used would have to be changed to the following:

     SELECT *
     FROM sales_order_items
     WHERE prod_id IN ( 401, 300 )
     ORDER BY ship_date DESC

The command using the subquery is an improvement because it still works even if data in the database is changed.

Example 3

As another example, you can list orders for everything except those products in short supply with the query:

     SELECT *
     FROM sales_order_items
     WHERE prod_id NOT IN
     ( SELECT id
     FROM product
     WHERE quantity < 20 )
     ORDER BY ship_date DESC