First Guide to SQL Anywhere Studio
PART 3. Basic SQL
CHAPTER 17. Introduction to Subqueries
Sometimes it is useful to use the results of one statement as part of another statement.
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.
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.
You can list all the order items for wool caps with the following query:
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.