Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Introduction to subqueries

A relational database allows you to store related data in more than one table. You can extract data from related tables using subqueries—queries that appear in another query's WHERE clause or HAVING clause. Subqueries make some queries easier to write than joins, and there are queries that cannot be written without using subqueries.

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.

For example, you store information particular to products in one table, Products, and information that pertains to sales orders in another table, SalesOrdersItems. The Products table contains the information about the various products. The SalesOrdersItems table contains information about customers' orders.

In general, only the simplest questions can be answered using only one table. For example, if the company reorders products when there are fewer than 50 of them in stock, then it is possible to answer the question "Which products are nearly out of stock?" with this query:

SELECT ID, Name, Description, Quantity
FROM Products
WHERE Quantity < 50;

However, if "nearly out of stock" depends on how many items of each type the typical customer orders, the number "50" will have to be replaced by a value obtained from the SalesOrderItems table.

A subquery is structured like a regular query, and appears in the main query's SELECT, FROM, WHERE, or HAVING clause. Continuing with the previous example, you can use a subquery to select the average number of items that a customer orders, and then use that figure in the main query to find products that are nearly out of stock. The following query finds the names and descriptions of the products which number less than twice the average number of items of each type that a customer orders.

SELECT Name, Description
FROM Products WHERE Quantity <  2 * (
   SELECT AVG( Quantity )
   FROM SalesOrderItems
   );

In the WHERE clause, subqueries help select the rows from the tables listed in the FROM clause that appear in the query results. In the HAVING clause, they help select the row groups, as specified by the main query's GROUP BY clause, that appear in the query results.

Following are three simple examples of using subqueries.

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

• In Interactive SQL, execute the following statement:

  SELECT ID, Description, Quantity
  FROM Products
  WHERE Quantity < 20;

  ID	Description	Quantity
  401	Wool cap	12

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

List all order items for wool caps

• In Interactive SQL, execute the following statement:

  SELECT *
  FROM SalesOrderItems
  WHERE ProductID = 401
  ORDER BY ShipDate DESC;

  ID	LineID	ProductID	Quantity	ShipDate
  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 products that are low in stock

• In Interactive SQL, execute the following statement:

  SELECT *
  FROM SalesOrderItems
  WHERE ProductID IN
     (  SELECT ID
         FROM Products
         WHERE Quantity < 20 )
  ORDER BY ShipDate DESC;

  ID	LineID	ProductID	Quantity	ShipDate
  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 Products
    WHERE Quantity < 20 );

The subquery makes a list of all values in the ID column in the Products 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.

---

Single-row and multiple-row subqueries
Using subqueries instead of joins