First Guide to SQL Anywhere Studio
   PART 3. Basic SQL
     CHAPTER 17. Introduction to Subqueries       

A simple subquery

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:

1. 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