Sams Teach Yourself SQL in 24 Hours

Recommended Book

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Recommended Book

Recommended Book 

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Embedding a Subquery Within a Subquery

A subquery can be embedded within another subquery, just as you can embed the subquery within a regular query. When a subquery is used, that subquery is resolved before the main query. Likewise, the lowest level subquery is resolved first in embedded or nested subqueries, working out to the main query.

The basic syntax for embedded subqueries is as follows:

   
SELECT COLUMN_NAME [, COLUMN_NAME ]
FROM TABLE1 [, TABLE2 ]
WHERE COLUMN_NAME OPERATOR (SELECT COLUMN_NAME
                            FROM TABLE
                            WHERE COLUMN_NAME OPERATOR
                                    (SELECT COLUMN_NAME
                                    FROM TABLE
                                    [ WHERE COLUMN_NAME OPERATOR VALUE ]))

The following example uses two subqueries, one embedded within the other. You want to find out what customers have placed orders where the quantity multiplied by the cost of a single order is greater than the sum of the cost of all products.

   
   

   SELECT CUST_ID, CUST_NAME

   FROM CUSTOMER_TBL

   WHERE CUST_ID IN (SELECT O.CUST_ID)
                  
   FROM, ORDERS_TBL O, PRODUCTS_TBL P
                  
   WHERE O PROD_ID = P.PROD_ID
                    
   AND O.QTY + P.COST < (SELECT SUM(COST)
                                          
   FROM
                                              
   PRODUCTS_TBL));

   
CUST_ID    CUST_NAME
---------- ------------------
090        WENDY WOLF
232        LESLIE GLEASON
287        GAVINS PLACE
43         SCHYLERS NOVELTIES
432        SCOTTYS MARKET
560        ANDYS CANDIES

6 rows selected.

Six rows that met the criteria of both subqueries were selected.

The following two examples show the results of each of the subqueries to aid your understanding of how the main query was resolved.

   

   SELECT SUM(COST) FROM PRODUCTS_TBL;

   
 SUM(COST)
----------
     138.08

1 row selected.

   

   SELECT O.CUST_ID

   FROM ORDERS_TBL O, PRODUCTS_TBL P

   WHERE O.PROD_ID = P.PROD_ID
  
   AND O.QTY * P.COST > 72.14;

   
CUST_ID
-------
43
287

2 rows selected.

In essence, the main query (after the resolution of the subqueries) is evaluated, as shown in the following example, the substitution of the second subquery:

   
   

   SELECT CUST_ID, CUST_NAME

   FROM CUSTOMER_TBL

   WHERE CUST_ID IN (SELECT O.CUST_ID
                  
   FROM ORDERS_TBL O, PRODUCTS_TBL P
                  
   WHERE O.PROD_ID = P.PROD_ID
                    
   AND O.QTY * P.COST > 72.14);

The following shows the substitution of the first subquery:

   

   SELECT CUST_ID, CUST_NAME

   FROM CUSTOMER_TBL

   WHERE CUST_ID IN ('287','43');

The following is the final result:

   
CUST_ID    CUST_NAME
---------- ------------------

43         SCHYLERS NOVELTIES
287        GAVINS PLACE

2 rows selected.

Correlated Subqueries

Correlated subqueries are common in many SQL implementations. The concept of correlated subqueries is discussed as an ANSI standard SQL topic and is covered briefly in this hour. A correlated subquery is a subquery that is dependent upon information in the main query. This means that tables in a subquery can be related to tables in the main query.

In the following example, the table join between CUSTOMER_TBL and ORDERS_TBL in the subquery is dependent on the alias for CUSTOMER_TBL (C) in the main query. This query returns the name of all customers that have ordered more than 10 units of one or more items.

   
   

   SELECT C.CUST_NAME

   FROM CUSTOMER_TBL C

   WHERE 10 < (SELECT SUM(O.QTY)
            
   FROM ORDERS_TBL O
            
   WHERE O.CUST_ID = C.CUST_ID);

   
CUST_NAME
------------------

SCOTTYS MARKET
SCHYLERS NOVELTIES
MARYS GIFT SHOP

The subquery is slightly modified in the next statement to show you the total quantity of units ordered for each customer, allowing the previous results to be verified.

   

   SELECT C.CUST_NAME, SUM(O.QTY)

   FROM CUSTOMER_TBL C,
     
   ORDERS_TBL O

   WHERE C.CUST_ID = O.CUST_ID

   GROUP BY C.CUST_NAME;

   
CUST_NAME                      SUM(O.QTY)
------------------------------ ----------
ANDYS CANDIES                           1
GAVINS PLACE                           10
LESLIE GLEASON                          1
MARYS GIFT SHOP                       100
SCHYLERS NOVELTIES                     25
SCOTTYS MARKET                         20
WENDY WOLF                              2

7 rows selected.

The GROUP BY clause in this example is required because another column is being selected with the aggregate function SUM. This gives you a sum for each customer. In the original subquery, a GROUP BY clause is not required because SUM is used to achieve a total for the entire query, which is run against the record for each individual customer.