Teach Yourself SQL in 21 Days, Second Edition
  
- Day 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 Day 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 Day 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 Day 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 Day 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
Today 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');
Exercise
- Write a query using the table ORDERS to return all the NAMEs
and ORDEREDON dates for every store that comes after JACKS BIKE
in the alphabet.
© Copyright, Macmillan Computer Publishing. All
rights reserved.
| 
