Introduction

ASA Getting Started
Selecting Data Using Subqueries

Introduction

Subqueries use the results of one query as part of another query. This section illustrates a situation where subqueries can be used by building a query that lists order items for products that are low in stock.

There are two queries involved in producing this list. This section first describes them separately, and then shows the single query that produces the same result.

List all products for which there are less than 20 items in stock

In Interactive SQL, type the following in the SQL Statements pane and press F5 to execute the statement.
```
SELECT id, description, quantity
FROM product
WHERE quantity < 20
```
id description quantity
401 Wool cap 12

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

id	description	quantity
401	Wool cap	12

List all order items for wool caps

In Interactive SQL, type the following in the SQL Statements pane and press F5 to execute the statement.

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	7/9/2001
2053	1	401	60	6/30/2001
2125	2	401	36	6/28/2001
2027	1	401	12	6/17/2001
...	...	...	...	...

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.

List all order items for items that are low in stock

In Interactive SQL, type the following in the SQL Statements pane and press F5 to execute the statement.

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	7/9/2001
2053	1	401	60	6/30/2001
2125	2	401	36	6/28/2001
2027	1	401	12	6/17/2001
...	...	...	...	...

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.

The subquery returns a set of rows, but only a single column. The IN keyword treats each value as a member of a set and tests whether each row in the main query is a member of the set.