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.

Note

You must check your particular implementation for limits on the number of subqueries, if any, that can be used in a single statement. It may differ between vendors.


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.

Caution

The use of multiple subqueries results in slower response time and may result in reduced accuracy of the results due to possible mistakes in the statement coding.


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.

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(0.QTY)
							FROM ORDERS_TBL 0
							WHERE C.CUST_ID = O.CUST_ID
						

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

SCOTTYS MARKET
SCHYLERS NOVELTIES
MARYS GIFT SHOP

Note

In the case of a correlated subquery, the reference to the table in the main query must be accomplished before the subquery can be resolved.


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(0.QTY)
							FROM CUSTOMER_TBL C,
							ORDERS_TBL O
							WHERE C. CUST_ID = O.CUST_TD
							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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset