Web based School

- Chapter 7 -
Subqueries: The Embedded SELECT Statement

Objectives

A subquery is a query whose results are passed as the argument for another query. Subqueries enable you to bind several queries together. By the end of the day, you will understand and be able to do the following:

Build a subquery
Use the keywords EXISTS, ANY, and ALL with your subqueries
Build and use correlated subqueries

NOTE: The examples for today's lesson were created using Borland's ISQL, the same implementation used on Chapter 6, "Joining Tables." Remember, this implementation does not use the SQL> prompt or line numbers.

Building a Subquery

Simply put, a subquery lets you tie the result set of one query to another. The general syntax is as follows:

SYNTAX:

SELECT *
FROM TABLE1
WHERE TABLE1.SOMECOLUMN =
(SELECT SOMEOTHERCOLUMN
FROM TABLE2
WHERE SOMEOTHERCOLUMN = SOMEVALUE)

Notice how the second query is nested inside the first. Here's a real-world example that uses the PART and ORDERS tables:

INPUT:

SELECT *
FROM PART

OUTPUT:

    PARTNUM DESCRIPTION                PRICE
=========== ==================== ===========

         54 PEDALS                     54.25
         42 SEATS                      24.50
         46 TIRES                      15.25
         23 MOUNTAIN BIKE             350.45
         76 ROAD BIKE                 530.00
         10 TANDEM                   1200.00

INPUT/OUTPUT:

SELECT *
FROM ORDERS

  ORDEREDON NAME           PARTNUM    QUANTITY REMARKS
=========== ========== =========== =========== ========

15-MAY-1996 TRUE WHEEL          23           6 PAID
19-MAY-1996 TRUE WHEEL          76           3 PAID
 2-SEP-1996 TRUE WHEEL          10           1 PAID
30-JUN-1996 TRUE WHEEL          42           8 PAID
30-JUN-1996 BIKE SPEC           54          10 PAID
30-MAY-1996 BIKE SPEC           10           2 PAID
30-MAY-1996 BIKE SPEC           23           8 PAID
17-JAN-1996 BIKE SPEC           76          11 PAID
17-JAN-1996 LE SHOPPE           76           5 PAID
 1-JUN-1996 LE SHOPPE           10           3 PAID
 1-JUN-1996 AAA BIKE            10           1 PAID
 1-JUL-1996 AAA BIKE            76           4 PAID
 1-JUL-1996 AAA BIKE            46          14 PAID
11-JUL-1996 JACKS BIKE          76          14 PAID

ANALYSIS:

The tables share a common field called PARTNUM. Suppose that you didn't know (or didn't want to know) the PARTNUM, but instead wanted to work with the description of the part. Using a subquery, you could type this:

INPUT/OUTPUT:

SELECT *
FROM ORDERS
WHERE PARTNUM =
(SELECT PARTNUM
FROM PART
WHERE DESCRIPTION LIKE "ROAD%")

  ORDEREDON NAME           PARTNUM    QUANTITY REMARKS
=========== ========== =========== =========== ========

19-MAY-1996 TRUE WHEEL          76           3 PAID
17-JAN-1996 BIKE SPEC           76          11 PAID
17-JAN-1996 LE SHOPPE           76           5 PAID
 1-JUL-1996 AAA BIKE            76           4 PAID
11-JUL-1996 JACKS BIKE          76          14 PAID

ANALYSIS:

Even better, if you use the concepts you learned on Chapter 6, you could enhance the PARTNUM column in the result by including the DESCRIPTION, making PARTNUM clearer for anyone who hasn't memorized it. Try this:

INPUT/OUTPUT:

SELECT O.ORDEREDON, O.PARTNUM,
P.DESCRIPTION, O.QUANTITY, O.REMARKS
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
AND
O.PARTNUM =
(SELECT PARTNUM
FROM PART
WHERE DESCRIPTION LIKE "ROAD%")

  ORDEREDON     PARTNUM DESCRIPTION       QUANTITY REMARKS
=========== =========== ============ =========== =========

19-MAY-1996          76 ROAD BIKE                3 PAID
 1-JUL-1996          76 ROAD BIKE                4 PAID
17-JAN-1996          76 ROAD BIKE                5 PAID
17-JAN-1996          76 ROAD BIKE               11 PAID
11-JUL-1996          76 ROAD BIKE               14 PAID

ANALYSIS:

The first part of the query is very familiar:

SELECT O.ORDEREDON, O.PARTNUM,
P.DESCRIPTION, O.QUANTITY, O.REMARKS
FROM ORDERS O, PART P

Here you are using the aliases O and P for tables ORDERS and PART to select the five columns you are interested in. In this case the aliases were not necessary because each of the columns you asked to return is unique. However, it is easier to make a readable query now than to have to figure it out later. The first WHERE clause you encounter

WHERE O.PARTNUM = P.PARTNUM

is standard language for the join of tables PART and ORDERS specified in the FROM clause. If you didn't use this WHERE clause, you would have all the possible row combinations of the two tables. The next section includes the subquery. The statement

AND
O.PARTNUM =
(SELECT PARTNUM
FROM PART
WHERE DESCRIPTION LIKE "ROAD%")

adds the qualification that O.PARTNUM must be equal to the result of your simple subquery. The subquery is straightforward, finding all the part numbers that are LIKE "ROAD%". The use of LIKE was somewhat lazy, saving you the keystrokes required to type ROAD BIKE. However, it turns out you were lucky this time. What if someone in the Parts department had added a new part called ROADKILL? The revised PART table would look like this:

INPUT/OUTPUT:

SELECT *
FROM PART

    PARTNUM DESCRIPTION                PRICE
=========== ==================== ===========

         54 PEDALS                     54.25
         42 SEATS                      24.50
         46 TIRES                      15.25
         23 MOUNTAIN BIKE             350.45
         76 ROAD BIKE                 530.00
         10 TANDEM                   1200.00
         77 ROADKILL                    7.99

Suppose you are blissfully unaware of this change and try your query after this new product was added. If you enter this:

SELECT O.ORDEREDON, O.PARTNUM,
P.DESCRIPTION, O.QUANTITY, O.REMARKS
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
AND
O.PARTNUM =
(SELECT PARTNUM
FROM PART
WHERE DESCRIPTION LIKE "ROAD%")

the SQL engine complains

multiple rows in singleton select

and you don't get any results. The response from your SQL engine may vary, but it still complains and returns nothing.

To find out why you get this undesirable result, assume the role of the SQL engine. You will probably evaluate the subquery first. You would return this:

INPUT/OUTPUT:

SELECT PARTNUM
FROM PART
WHERE DESCRIPTION LIKE "ROAD%"

    PARTNUM
===========

         76
         77

You would take this result and apply it to O.PARTNUM =, which is the step that causes the problem.

ANALYSIS:

How can PARTNUM be equal to both 76 and 77? This must be what the engine meant when it accused you of being a simpleton. When you used the LIKE clause, you opened yourself up for this error. When you combine the results of a relational operator with another relational operator, such as =, <, or >, you need to make sure the result is singular. In the case of the example we have been using, the solution would be to rewrite the query using an = instead of the LIKE, like this:

INPUT/OUTPUT:

SELECT O.ORDEREDON, O.PARTNUM,
P.DESCRIPTION, O.QUANTITY, O.REMARKS
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
AND
O.PARTNUM =
(SELECT PARTNUM
FROM PART
WHERE DESCRIPTION = "ROAD BIKE")

  ORDEREDON     PARTNUM DESCRIPTION        QUANTITY REMARKS
=========== =========== =============== =========== ==========

19-MAY-1996          76 ROAD BIKE                 3 PAID
 1-JUL-1996          76 ROAD BIKE                 4 PAID
17-JAN-1996          76 ROAD BIKE                 5 PAID
17-JAN-1996          76 ROAD BIKE                11 PAID
11-JUL-1996          76 ROAD BIKE                14 PAID

ANALYSIS:

This subquery returns only one unique result; therefore narrowing your = condition to a single value. How can you be sure the subquery won't return multiple values if you are looking for only one value?

Avoiding the use of LIKE is a start. Another approach is to ensure the uniqueness of the search field during table design. If you are the untrusting type, you could use the method (described yesterday) for joining a table to itself to check a given field for uniqueness. If you design the table yourself (see Chapter 9, "Creating and Maintaining Tables") or trust the person who designed the table, you could require the column you are searching to have a unique value. You could also use a part of SQL that returns only one answer: the aggregate function.

Using Aggregate Functions with Subqueries

The aggregate functions SUM, COUNT, MIN, MAX, and AVG all return a single value. To find the average amount of an order, type this:

INPUT:

SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM

OUTPUT:

        AVG
===========

    2419.16

ANALYSIS:

This statement returns only one value. To find out which orders were above average, use the preceding SELECT statement for your subquery. The complete query and result are as follows:

INPUT/OUTPUT:

SELECT O.NAME, O.ORDEREDON,
O.QUANTITY * P.PRICE TOTAL
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
AND
O.QUANTITY * P.PRICE  >
(SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM)

NAME         ORDEREDON       TOTAL
========== =========== ===========

LE SHOPPE   1-JUN-1996     3600.00
BIKE SPEC  30-MAY-1996     2803.60
LE SHOPPE  17-JAN-1996     2650.00
BIKE SPEC  17-JAN-1996     5830.00
JACKS BIKE 11-JUL-1996     7420.00

ANALYSIS:

This example contains a rather unremarkable SELECT/FROM/WHERE clause:

SELECT O.NAME, O.ORDEREDON,
O.QUANTITY * P.PRICE TOTAL
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM

These lines represent the common way of joining these two tables. This join is necessary because the price is in PART and the quantity is in ORDERS. The WHERE ensures that you examine only the join-formed rows that are related. You then add the subquery:

AND
O.QUANTITY * P.PRICE  >
(SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM)

The preceding condition compares the total of each order with the average you computed in the subquery. Note that the join in the subquery is required for the same reasons as in the main SELECT statement. This join is also constructed exactly the same way. There are no secret handshakes in subqueries; they have exactly the same syntax as a standalone query. In fact, most subqueries start out as standalone queries and are incorporated as subqueries after their results are tested.

Nested Subqueries

Nesting is the act of embedding a subquery within another subquery. For example:

Select * FROM SOMETHING WHERE ( SUBQUERY(SUBQUERY(SUBQUERY)));

Subqueries can be nested as deeply as your implementation of SQL allows. For example, to send out special notices to customers who spend more than the average amount of money, you would combine the information in the table CUSTOMER

INPUT:

SELECT *
FROM CUSTOMER

OUTPUT:

NAME       ADDRESS    STATE  ZIP        PHONE       REMARKS
========== ========== ====== ========== =========== ==========

TRUE WHEEL 55O HUSKER NE     58702      555-4545    NONE
BIKE SPEC  CPT SHRIVE LA     45678      555-1234    NONE
LE SHOPPE  HOMETOWN   KS     54678      555-1278    NONE
AAA BIKE   10 OLDTOWN NE     56784      555-3421    JOHN-MGR
JACKS BIKE 24 EGLIN   FL     34567      555-2314    NONE

with a slightly modified version of the query you used to find the above-average orders:

INPUT/OUTPUT:

SELECT ALL C.NAME, C.ADDRESS, C.STATE,C.ZIP
FROM CUSTOMER C
WHERE C.NAME IN
(SELECT O.NAME
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
AND
O.QUANTITY * P.PRICE  >
(SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM))

NAME       ADDRESS    STATE  ZIP
========== ========== ====== ==========

BIKE SPEC  CPT SHRIVE LA     45678
LE SHOPPE  HOMETOWN   KS     54678
JACKS BIKE 24 EGLIN   FL     34567

ANALYSIS:

Here's a look at what you asked for. In the innermost set of parentheses, you find a familiar statement:

SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM

This result feeds into a slightly modified version of the SELECT clause you used before:

SELECT O.NAME
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
AND
O.QUANTITY * P.PRICE  >

(...)

Note the SELECT clause has been modified to return a single column, NAME, which, not so coincidentally, is common with the table CUSTOMER. Running this statement by itself you get:

INPUT/OUTPUT:

SELECT O.NAME
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
AND
O.QUANTITY * P.PRICE  >
(SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM)

NAME
==========

LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE

ANALYSIS:

We just spent some time discussing why your subqueries should return just one value. The reason this query was able to return more than one value becomes apparent in a moment.

You bring these results to the statement:

SELECT C.NAME, C.ADDRESS, C.STATE,C.ZIP
FROM CUSTOMER C
WHERE C.NAME IN

(...)

ANALYSIS:

The first two lines are unremarkable. The third reintroduces the keyword IN, last seen on Chapter 2, "Introduction to the Query: The SELECT Statement." IN is the tool that enables you to use the multiple-row output of your subquery. IN, as you remember, looks for matches in the following set of values enclosed by parentheses, which in the this case produces the following values:

LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE

This subquery provides the conditions that give you the mailing list:

NAME       ADDRESS    STATE  ZIP
========== ========== ====== ======

BIKE SPEC  CPT SHRIVE LA     45678
LE SHOPPE  HOMETOWN   KS     54678
JACKS BIKE 24 EGLIN   FL     34567

This use of IN is very common in subqueries. Because IN uses a set of values for its comparison, it does not cause the SQL engine to feel conflicted and inadequate.

Subqueries can also be used with GROUP BY and HAVING clauses. Examine the following query:

INPUT/OUTPUT:

SELECT NAME, AVG(QUANTITY)
FROM ORDERS
GROUP BY NAME
HAVING AVG(QUANTITY) >
(SELECT AVG(QUANTITY)
FROM ORDERS)

NAME               AVG
========== ===========

BIKE SPEC            8
JACKS BIKE          14

ANALYSIS:

Let's examine this query in the order the SQL engine would. First, look at the subquery:

INPUT/OUTPUT:

SELECT AVG(QUANTITY)
FROM ORDERS

        AVG
===========

          6

By itself, the query is as follows:

INPUT/OUTPUT:

SELECT NAME, AVG(QUANTITY)
FROM ORDERS
GROUP BY NAME

NAME               AVG
========== ===========

AAA BIKE             6
BIKE SPEC            8
JACKS BIKE          14
LE SHOPPE            4
TRUE WHEEL           5

When combined through the HAVING clause, the subquery produces two rows that have above-average QUANTITY.

INPUT/OUTPUT:

HAVING AVG(QUANTITY) >
(SELECT AVG(QUANTITY)
FROM ORDERS)

NAME               AVG
========== ===========

BIKE SPEC            8
JACKS BIKE          14

Correlated Subqueries

The subqueries you have written so far are self-contained. None of them have used a reference from outside the subquery. Correlated subqueries enable you to use an outside reference with some strange and wonderful results. Look at the following query:

INPUT:

SELECT *
FROM ORDERS O
WHERE 'ROAD BIKE' =
(SELECT DESCRIPTION
FROM PART P
WHERE P.PARTNUM = O.PARTNUM)

OUTPUT:

  ORDEREDON NAME           PARTNUM    QUANTITY REMARKS
=========== ========== =========== =========== ==========

19-MAY-1996 TRUE WHEEL          76           3 PAID
17-JAN-1996 BIKE SPEC           76          11 PAID
17-JAN-1996 LE SHOPPE           76           5 PAID
 1-JUL-1996 AAA BIKE            76           4 PAID

11-JUL-1996 JACKS BIKE          76          14 PAID

This query actually resembles the following JOIN:

INPUT:

SELECT O.ORDEREDON, O.NAME,
O.PARTNUM, O.QUANTITY, O.REMARKS
FROM ORDERS O, PART P
WHERE P.PARTNUM = O.PARTNUM
AND P.DESCRIPTION = 'ROAD BIKE'

OUTPUT:

  ORDEREDON NAME           PARTNUM    QUANTITY REMARKS
=========== ========== =========== =========== =======

19-MAY-1996 TRUE WHEEL          76           3 PAID
 1-JUL-1996 AAA BIKE            76           4 PAID
17-JAN-1996 LE SHOPPE           76           5 PAID
17-JAN-1996 BIKE SPEC           76          11 PAID
11-JUL-1996 JACKS BIKE          76          14 PAID

ANALYSIS:

In fact, except for the order, the results are identical. The correlated subquery acts very much like a join. The correlation is established by using an element from the query in the subquery. In this example the correlation was established by the statement

WHERE P.PARTNUM = O.PARTNUM

in which you compare P.PARTNUM, from the table inside your subquery, to O.PARTNUM, from the table outside your query. Because O.PARTNUM can have a different value for every row, the correlated subquery is executed for each row in the query. In the next example each row in the table ORDERS

INPUT/OUTPUT:

SELECT *
FROM ORDERS

  ORDEREDON NAME           PARTNUM    QUANTITY REMARKS
=========== ========== =========== =========== =======

15-MAY-1996 TRUE WHEEL          23           6 PAID
19-MAY-1996 TRUE WHEEL          76           3 PAID
 2-SEP-1996 TRUE WHEEL          10           1 PAID
30-JUN-1996 TRUE WHEEL          42           8 PAID
30-JUN-1996 BIKE SPEC           54          10 PAID
30-MAY-1996 BIKE SPEC           10           2 PAID
30-MAY-1996 BIKE SPEC           23           8 PAID
17-JAN-1996 BIKE SPEC           76          11 PAID
17-JAN-1996 LE SHOPPE           76           5 PAID
 1-JUN-1996 LE SHOPPE           10           3 PAID
 1-JUN-1996 AAA BIKE            10           1 PAID
 1-JUL-1996 AAA BIKE            76           4 PAID
 1-JUL-1996 AAA BIKE            46          14 PAID
11-JUL-1996 JACKS BIKE          76          14 PAID

is processed against the subquery criteria:

SELECT DESCRIPTION
FROM PART P
WHERE P.PARTNUM = O.PARTNUM

ANALYSIS:

This operation returns the DESCRIPTION of every row in PART where P.PARTNUM = O.PARTNUM. These descriptions are then compared in the WHERE clause:

WHERE 'ROAD BIKE' =

Because each row is examined, the subquery in a correlated subquery can have more than one value. However, don't try to return multiple columns or columns that don't make sense in the context of the WHERE clause. The values returned still must match up against the operation specified in the WHERE clause. For example, in the query you just did, returning the PRICE to compare with ROAD BIKE would have the following result:

INPUT/OUTPUT:

SELECT *
FROM ORDERS O
WHERE 'ROAD BIKE' =
(SELECT PRICE
FROM PART P
WHERE P.PARTNUM = O.PARTNUM)

conversion error from string "ROAD BIKE"

Here's another example of something not to do:

SELECT *
FROM ORDERS O
WHERE 'ROAD BIKE' =
(SELECT *
FROM PART P
WHERE P.PARTNUM = O.PARTNUM)

ANALYSIS:

This SELECT caused a General Protection Fault on my Windows operating system. The SQL engine simply can't correlate all the columns in PART with the operator =.

Correlated subqueries can also be used with the GROUP BY and HAVING clauses. The following query uses a correlated subquery to find the average total order for a particular part and then applies that average value to filter the total order grouped by PARTNUM:

INPUT/OUTPUT:

SELECT O.PARTNUM, SUM(O.QUANTITY*P.PRICE), COUNT(PARTNUM)
FROM ORDERS O, PART P
WHERE P.PARTNUM = O.PARTNUM
GROUP BY O.PARTNUM
HAVING SUM(O.QUANTITY*P.PRICE) >
(SELECT AVG(O1.QUANTITY*P1.PRICE)
FROM PART P1, ORDERS O1
WHERE P1.PARTNUM = O1.PARTNUM
AND P1.PARTNUM = O.PARTNUM)

    PARTNUM         SUM       COUNT
=========== =========== ===========

         10     8400.00           4
         23     4906.30           2
         76    19610.00           5

ANALYSIS:

The subquery does not just compute one

AVG(O1.QUANTITY*P1.PRICE)

Because of the correlation between the query and the subquery,

AND P1.PARTNUM = O.PARTNUM

this average is computed for every group of parts and then compared:

HAVING SUM(O.QUANTITY*P.PRICE) >

TIP: When using correlated subqueries with GROUP BY and HAVING, the columns in the HAVING clause must exist in either the SELECT clause or the GROUP BY clause. Otherwise, you get an error message along the lines of invalid column reference because the subquery is evoked for each group, not each row. You cannot make a valid comparison to something that is not used in forming the group.

Using EXISTS, ANY, and ALL

The usage of the keywords EXISTS, ANY, and ALL is not intuitively obvious to the casual observer. EXISTS takes a subquery as an argument and returns TRUE if the subquery returns anything and FALSE if the result set is empty. For example:

INPUT/OUTPUT:

SELECT NAME, ORDEREDON
FROM ORDERS 
WHERE EXISTS
(SELECT *
FROM ORDERS
WHERE NAME ='TRUE WHEEL')

NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
BIKE SPEC  30-JUN-1996
BIKE SPEC  30-MAY-1996
BIKE SPEC  30-MAY-1996
BIKE SPEC  17-JAN-1996
LE SHOPPE  17-JAN-1996
LE SHOPPE   1-JUN-1996
AAA BIKE    1-JUN-1996
AAA BIKE    1-JUL-1996
AAA BIKE    1-JUL-1996
JACKS BIKE 11-JUL-1996

ANALYSIS:

Not what you might expect. The subquery inside EXISTS is evaluated only once in this uncorrelated example. Because the return from the subquery has at least one row, EXISTS evaluates to TRUE and all the rows in the query are printed. If you change the subquery as shown next, you don't get back any results.

SELECT NAME, ORDEREDON
FROM ORDERS
WHERE EXISTS
(SELECT *
FROM ORDERS
WHERE NAME ='MOSTLY HARMLESS')

ANALYSIS:

EXISTS evaluates to FALSE. The subquery does not generate a result set because MOSTLY HARMLESS is not one of your names.

NOTE: Notice the use of SELECT * in the subquery inside the EXISTS. EXISTS does not care how many columns are returned.

You could use EXISTS in this way to check on the existence of certain rows and control the output of your query based on whether they exist.

If you use EXISTS in a correlated subquery, it is evaluated for every case implied by the correlation you set up. For example:

INPUT/OUTPUT:

SELECT NAME, ORDEREDON
FROM ORDERS O
WHERE EXISTS
(SELECT *
FROM CUSTOMER C
WHERE STATE ='NE'
AND C.NAME = O.NAME)

NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
AAA BIKE    1-JUN-1996
AAA BIKE    1-JUL-1996
AAA BIKE    1-JUL-1996

This slight modification of your first, uncorrelated query returns all the bike shops from Nebraska that made orders. The following subquery is run for every row in the query correlated on the CUSTOMER name and ORDERS name:

(SELECT *
FROM CUSTOMER C
WHERE STATE ='NE'
AND C.NAME = O.NAME)

ANALYSIS:

EXISTS is TRUE for those rows that have corresponding names in CUSTOMER located in NE. Otherwise, it returns FALSE.

Closely related to EXISTS are the keywords ANY, ALL, and SOME. ANY and SOME are identical in function. An optimist would say this feature provides the user with a choice. A pessimist would see this condition as one more complication. Look at this query:

INPUT:

SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME = ANY
(SELECT NAME
FROM ORDERS
WHERE NAME ='TRUE WHEEL')

OUTPUT:

NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996

ANALYSIS:

ANY compared the output of the following subquery to each row in the query, returning TRUE for each row of the query that has a result from the subquery.

(SELECT NAME
FROM ORDERS
WHERE NAME ='TRUE WHEEL')

Replacing ANY with SOME produces an identical result:

INPUT/OUTPUT:

SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME = SOME
(SELECT NAME
FROM ORDERS
WHERE NAME ='TRUE WHEEL')

NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996

ANALYSIS:

You may have already noticed the similarity to IN. The same query using IN is as follows:

INPUT/OUTPUT:

SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME IN
(SELECT NAME
FROM ORDERS
WHERE NAME ='TRUE WHEEL')

NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996

ANALYSIS:

As you can see, IN returns the same result as ANY and SOME. Has the world gone mad? Not yet. Can IN do this?

INPUT/OUTPUT:

SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME > ANY
(SELECT NAME
FROM ORDERS
WHERE NAME ='JACKS BIKE')

NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
LE SHOPPE  17-JAN-1996
LE SHOPPE   1-JUN-1996

The answer is no. IN works like multiple equals. ANY and SOME can be used with other relational operators such as greater than or less than. Add this tool to your kit.

ALL returns TRUE only if all the results of a subquery meet the condition. Oddly enough, ALL is used most commonly as a double negative, as in this query:

INPUT/OUTPUT:

SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME <> ALL
(SELECT NAME
FROM ORDERS
WHERE NAME ='JACKS BIKE')

NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
BIKE SPEC  30-JUN-1996
BIKE SPEC  30-MAY-1996
BIKE SPEC  30-MAY-1996
BIKE SPEC  17-JAN-1996
LE SHOPPE  17-JAN-1996
LE SHOPPE   1-JUN-1996
AAA BIKE    1-JUN-1996
AAA BIKE    1-JUL-1996
AAA BIKE    1-JUL-1996

ANALYSIS:

This statement returns everybody except JACKS BIKE. <>ALL evaluates to TRUE only if the result set does not contain what is on the left of the <>.

Summary

ToChapter you performed dozens of exercises involving subqueries. You learned how to use one of the most important parts of SQL. You also tackled one of the most difficult parts of SQL: a correlated subquery. The correlated subquery creates a relationship between the query and the subquery that is evaluated for every instance of that relationship. Don't be intimidated by the length of the queries. You can easily examine them one subquery at a time.

Q&A

Q In some cases SQL offers several ways to get the same result. Isn't this flexibility confusing?

A No, not really. Having so many ways to achieve the same result enables you to create some really neat statements. Flexibility is the virtue of SQL.

Workshop

The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."

Quiz

1. In the section on nested subqueries, the sample subquery returned several values:

LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE

Some of these are duplicates. Why aren't these duplicates in the final result set?

2. Are the following statements true or false?
The aggregate functions SUM, COUNT, MIN, MAX, and AVG all return multiple values.
The maximum number of subqueries that can be nested is two.
Correlated subqueries are completely self-contained.

3. Will the following subqueries work using the ORDERS table and the PART table?

INPUT/OUTPUT:

   SQL> SELECT *
        FROM PART;

        PARTNUM  DESCRIPTION     PRICE
             54  PEDALS          54.25
             42  SEATS           24.50
             46  TIRES           15.25
             23  MOUNTAIN BIKE  350.45
             76  ROAD BIKE      530.00
             10  TANDEM        1200.00

   6 rows selected.

INPUT/OUTPUT:

          SQL> SELECT *
               FROM ORDERS;

      ORDEREDON     NAME      PARTNUM   QUANITY  REMARKS
      15-MAY-96     TRUE WHEEL     23         6  PAID 
      19-MAY-96     TRUE WHEEL     76         3  PAID
      2-SEP-96      TRUE WHEEL     10         1  PAID
      30-JUN-96     BIKE SPEC      54        10  PAID
      30-MAY-96     BIKE SPEC      10         2  PAID
      30-MAY-96     BIKE SPEC      23         8  PAID
      17-JAN-96     BIKE SPEC      76        11  PAID
      17-JAN-96     LE SHOPPE      76         5  PAID
      1-JUN-96      LE SHOPPE      10         3  PAID
      1-JUN-96      AAA BIKE       10         1  PAID
      1-JUN-96      AAA BIKE       76         4  PAID
      1-JUN-96      AAA BIKE       46        14  PAID
      11-JUL-96     JACKS BIKE     76        14  PAID
      13 rows selected.

a. SQL> SELECT * FROM ORDERS

WHERE PARTNUM = SELECT PARTNUM FROM PART WHERE DESCRIPTION = 'TRUE WHEEL';

b. SQL> SELECT PARTNUM

FROM ORDERS WHERE PARTNUM = (SELECT * FROM PART WHERE DESCRIPTION = 'LE SHOPPE');

c. SQL> SELECT NAME, PARTNUM

FROM ORDERS WHERE EXISTS (SELECT * FROM ORDERS WHERE NAME = 'TRUE WHEEL');