Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

• Table of Contents
  • Copyright
  • About the Authors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • Part I: A SQL Concepts Overview
  • Hour 1. Welcome to the World of SQL
  • Part II: Building Your Database
  • Hour 2. Defining Data Structures
  • Hour 3. Managing Database Objects
  • Hour 4. The Normalization Process
  • Hour 5. Manipulating Data
  • Hour 6. Managing Database Transactions
  • Part III: Getting Effective Results from Queries
  • Hour 7. Introduction to the Database Query
  • Hour 8. Using Operators to Categorize Data
  • Hour 9. Summarizing Data Results from a Query
  • Hour 10. Sorting and Grouping Data
  • Hour 11. Restructuring the Appearance of Data
  • Hour 12. Understanding Dates and Times
  • Part IV: Building Sophisticated Database Queries
  • Hour 13. Joining Tables in Queries
  • Hour 14. Using Subqueries to Define Unknown Data
    • What Is a Subquery?
    • Embedding a Subquery Within a Subquery
    • Summary
    • Q&A
    • Workshop
  • Hour 15. Combining Multiple Queries into One
  • Part V: SQL Performance Tuning
  • Hour 16. Using Indexes to Improve Performance
  • Hour 17. Improving Database Performance
  • Part VI: Using SQL to Manage Users and Security
  • Hour 18. Managing Database Users
  • Hour 19. Managing Database Security
  • Part VII: Summarized Data Structures
  • Hour 20. Creating and Using Views and Synonyms
  • Hour 21. Working with the System Catalog
  • Part VIII: Applying SQL Fundamentals in Today's World
  • Hour 22. Advanced SQL Topics
  • Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
  • Hour 24. Extensions to Standard SQL
  • Part IX: Appendixes
  • Appendix A. Common SQL Commands
  • Appendix B. Using MySQL for Exercises
  • Appendix C. Answers to Quizzes and Exercises
  • Appendix D. CREATE TABLE Statements for Book Examples
  • Appendix E. INSERT Statements for Data in Book Examples
  • Appendix F. Glossary
  • Appendix G. Bonus Exercises

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.

Share This