Database Reference
In-Depth Information
SUBQUERIES
In some cases, it is useful to obtain the results you want in two stages. You can do so by placing one query
inside another. The inner query is called a subquery and is evaluated first. After the subquery has been eval-
uated, the outer query can be evaluated. Example 21 illustrates the process.
95
EXAMPLE 21
List the order number for each order that contains an order line for a part located in warehouse 3.
You can find the answer by using the Part table and creating a list of part numbers for those parts in
warehouse 3. Then you can use the OrderLine table to find those order numbers present in any row on
which the part number is in the results you created in the inner query. The corresponding query design
appears in Figure 3-41.
Part number must be
in results of subquery
Subquery
FIGURE 3-41
SQL query with a subquery
NOTE
Although not required, it is common to enclose subqueries in parentheses for readability.
The query results appear in Figure 3-42.
FIGURE 3-42
Query results
The subquery finds all the part numbers in the Part table with a warehouse number of 3. The subquery
is evaluated first, producing a list of part numbers. After the subquery has been evaluated, the outer query is
evaluated. Order numbers in the results appear in any row in the OrderLine table for which the part number
in the row is in the subquery results.
