Java Reference
In-Depth Information
WHERE
(SUBQUERY
(SUBQUERY
(SUBQUERY)));
For example, to send out special notices to customers who spend more than the average amount of
money, you could build a customer list by creating a query using two nested subqueries, as shown in
Figure 7-4
.
Figure 7-4:
Using nested subqueries
Testing Subqueries
Recall that one of the nice things about subqueries is that they are easy to test as queries before
plugging them into larger queries. For example, the subquery that calculates the average cost of a
purchase is very straightforward. Here's an example:
(SELECT AVG(oi.Qty * i.Cost)
FROM Ordered_Items oi, Inventory i
WHERE oi.Item_Number = i.Item_Number)
In
Figure 7-5
you see the two subqueries combined to generate a list of all purchases above the
average cost. Note that the additional columns oi.Order_Number and oi.Qty * i.Cost AS 'Total' have
been added to make it easier to check the queries.
