Writing Nested Subqueries

You can nest subqueries inside other subqueries to a depth of 255. But you should use this technique sparingly; you may find your query performs better using table joins. The following example contains a nested subquery. Notice that it is contained within a subquery, which is itself contained in an outer query:

  SELECT product_type_id, AVG(price)   FROM products   GROUP BY product_type_id   HAVING AVG(price)  <  (SELECT MAX(AVG(price))   FROM products   WHERE product_type_id IN   (SELECT product_id   FROM purchases   WHERE quantity  >  1)   GROUP BY product_type_id);  PRODUCT_TYPE_ID AVG(PRICE) --------------- ----------  1 24.975  3 13.24  4 13.99  13.49 

As you can see, this example is quite complex and contains three queries: a nested subquery, a subquery, and the outer query. These query parts are run in that order. Let s break the example down into the three parts and examine the results returned. The nested subquery is as follows :

 SELECT product_id FROM purchases WHERE quantity > 1 

This subquery returns the product_id values for the products that have been purchased more than once. The rows returned by this subquery are

 PRODUCT_ID ----------  2  1 

The subquery that receives this output is

 SELECT MAX(AVG(price)) FROM products WHERE product_type_id IN  (... output from previous nested subquery ...) GROUP BY product_type_id 

This subquery returns the maximum of the averages of the prices for the products returned by the previous nested subquery. The row returned is

 MAX(AVG(PRICE)) ---------------  26.22 

This row is returned to the following outer query:

 SELECT product_type_id, AVG(price) FROM products GROUP BY product_type_id HAVING AVG(price) <  (... output from previous subquery ...); 

This query returns the product_type_id and average price of products that are less than average returned by the previous subquery. The rows returned are

 PRODUCT_TYPE_ID AVG(PRICE) --------------- ----------  1 24.975  3 13.24  4 13.99  13.49 

These are, of course, the rows returned by the complete query shown earlier.