Embedding a Subquery Within a Subquery | Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)


Team-Fly

	Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
	Table of Contents

	Hour 14. Using Subqueries to Define Unknown Data

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.

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.

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. 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

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(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.


Team-Fly

Top