ch07.htm

21精通SQL

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
</FONT></PRE>
<P>with a slightly modified version of the query you used to find the above-average
orders:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>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  &gt;
(SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM))</B>

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

BIKE SPEC  CPT SHRIVE LA     45678
LE SHOPPE  HOMETOWN   KS     54678
JACKS BIKE 24 EGLIN   FL     34567
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Here's a look at what you asked for. In the innermost set of parentheses, you
find a familiar statement:</P>
<PRE><FONT COLOR="#0066FF">SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
</FONT></PRE>
<P>This result feeds into a slightly modified version of the <TT>SELECT</TT> clause
you used before:</P>
<PRE><FONT COLOR="#0066FF">SELECT O.NAME
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
AND
O.QUANTITY * P.PRICE  &gt;

(...)
</FONT></PRE>
<P>Note the <TT>SELECT</TT> clause has been modified to return a single column, <TT>NAME</TT>,
which, not so coincidentally, is common with the table <TT>CUSTOMER</TT>. Running
this statement by itself you get:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT O.NAME
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
AND
O.QUANTITY * P.PRICE  &gt;
(SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM)</B>

NAME
==========

LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>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.</P>
<P>You bring these results to the statement:</P>
<PRE><FONT COLOR="#0066FF">SELECT C.NAME, C.ADDRESS, C.STATE,C.ZIP
FROM CUSTOMER C
WHERE C.NAME IN
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">(...)
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The first two lines are unremarkable. The third reintroduces the keyword <TT>IN</TT>,
last seen on Day 2, &quot;Introduction to the Query: The <TT>SELECT</TT> Statement.&quot;
<TT>IN</TT> is the tool that enables you to use the multiple-row output of your subquery.
<TT>IN</TT>, as you remember, looks for matches in the following set of values enclosed
by parentheses, which in the this case produces the following values:</P>
<PRE><FONT COLOR="#0066FF">LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE
</FONT></PRE>
<P>This subquery provides the conditions that give you the mailing list:</P>
<PRE><FONT COLOR="#0066FF">NAME       ADDRESS    STATE  ZIP
========== ========== ====== ======

BIKE SPEC  CPT SHRIVE LA     45678
LE SHOPPE  HOMETOWN   KS     54678
JACKS BIKE 24 EGLIN   FL     34567
</FONT></PRE>
<P>This use of <TT>IN</TT> is very common in subqueries. Because <TT>IN</TT> uses
a set of values for its comparison, it does not cause the SQL engine to feel conflicted
and inadequate.</P>
<P>Subqueries can also be used with <TT>GROUP BY</TT> and <TT>HAVING</TT> clauses.
Examine the following query:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, AVG(QUANTITY)
FROM ORDERS
GROUP BY NAME
HAVING AVG(QUANTITY) &gt;
(SELECT AVG(QUANTITY)
FROM ORDERS)</B>

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

BIKE SPEC            8
JACKS BIKE          14
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Let's examine this query in the order the SQL engine would. First, look at the
subquery:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT AVG(QUANTITY)
FROM ORDERS
</B>
        AVG
===========

          6
</FONT></PRE>
<P>By itself, the query is as follows:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, AVG(QUANTITY)
FROM ORDERS
GROUP BY NAME</B>

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

AAA BIKE             6
BIKE SPEC            8
JACKS BIKE          14
LE SHOPPE            4
TRUE WHEEL           5
</FONT></PRE>
<P>When combined through the <TT>HAVING</TT> clause, the subquery produces two rows
that have above-average <TT>QUANTITY</TT>.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>HAVING AVG(QUANTITY) &gt;
(SELECT AVG(QUANTITY)
FROM ORDERS)</B>

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

BIKE SPEC            8
JACKS BIKE          14
</FONT></PRE>
<H2><FONT COLOR="#000077">Correlated Subqueries</FONT></H2>
<P>The subqueries you have written so far are <I>self-contained</I>. None of them
have used a reference from outside the subquery. <I>Correlated subqueries</I> enable
you to use an outside reference with some strange and wonderful results. Look at
the following query:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT *
FROM ORDERS O
WHERE 'ROAD BIKE' =
(SELECT DESCRIPTION
FROM PART P
WHERE P.PARTNUM = O.PARTNUM)</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">  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
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">11-JUL-1996 JACKS BIKE          76          14 PAID
</FONT></PRE>
<P>This query actually resembles the following <TT>JOIN</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>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'</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">  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
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>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</P>
<PRE><FONT COLOR="#0066FF">WHERE P.PARTNUM = O.PARTNUM
</FONT></PRE>
<P>in which you compare <TT>P.PARTNUM</TT>, from the table inside your subquery,
to <TT>O.PARTNUM</TT>, from the table outside your query. Because <TT>O.PARTNUM</TT>
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 <TT>ORDERS</TT></P>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT *
FROM ORDERS
</B>
  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
</FONT></PRE>
<P>is processed against the subquery criteria:</P>
<PRE><FONT COLOR="#0066FF">SELECT DESCRIPTION
FROM PART P
WHERE P.PARTNUM = O.PARTNUM
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This operation returns the <TT>DESCRIPTION</TT> of every row in <TT>PART</TT>
where <TT>P.PARTNUM = O.PARTNUM</TT>. These descriptions are then compared in the
<TT>WHERE</TT> clause:</P>
<PRE><FONT COLOR="#0066FF">WHERE 'ROAD BIKE' =
</FONT></PRE>
<P>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 <TT>WHERE</TT> clause. The values returned still
must match up against the operation specified in the <TT>WHERE</TT> clause. For example,
in the query you just did, returning the <TT>PRICE</TT> to compare with <TT>ROAD
BIKE</TT> would have the following result:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT *
FROM ORDERS O
WHERE 'ROAD BIKE' =
(SELECT PRICE
FROM PART P
WHERE P.PARTNUM = O.PARTNUM)</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">conversion error from string &quot;ROAD BIKE&quot;
</FONT></PRE>
<P>Here's another example of something not to do:</P>
<PRE><FONT COLOR="#0066FF">SELECT *
FROM ORDERS O
WHERE 'ROAD BIKE' =
(SELECT *
FROM PART P
WHERE P.PARTNUM = O.PARTNUM)
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This <TT>SELECT</TT> caused a General Protection Fault on my Windows operating
system. The SQL engine simply can't correlate all the columns in <TT>PART</TT> with
the operator <TT>=</TT>.</P>
<P>Correlated subqueries can also be used with the <TT>GROUP BY</TT> and <TT>HAVING</TT>
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 <TT>PARTNUM</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>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) &gt;
(SELECT AVG(O1.QUANTITY*P1.PRICE)
FROM PART P1, ORDERS O1
WHERE P1.PARTNUM = O1.PARTNUM
AND P1.PARTNUM = O.PARTNUM)</B>

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

         10     8400.00           4
         23     4906.30           2
         76    19610.00           5
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The subquery does not just compute one</P>
<PRE><FONT COLOR="#0066FF">AVG(O1.QUANTITY*P1.PRICE)
</FONT></PRE>
<P>Because of the correlation between the query and the subquery,</P>
<PRE><FONT COLOR="#0066FF">AND P1.PARTNUM = O.PARTNUM
</FONT></PRE>
<P>this average is computed for every group of parts and then compared:</P>
<PRE><FONT COLOR="#0066FF">HAVING SUM(O.QUANTITY*P.PRICE) &gt;
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>When using correlated subqueries
	with <TT>GROUP BY</TT> and <TT>HAVING</TT>, the columns in the <TT>HAVING</TT> clause
	must exist in either the <TT>SELECT</TT> clause or the <TT>GROUP BY</TT> clause.
	Otherwise, you get an error message along the lines of <TT>invalid column reference</TT>
	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. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">Using EXISTS, ANY, and ALL</FONT></H2>
<P>The usage of the keywords <TT>EXISTS</TT>, <TT>ANY</TT>, and <TT>ALL</TT> is not
intuitively obvious to the casual observer. <TT>EXISTS</TT> takes a subquery as an
argument and returns <TT>TRUE</TT> if the subquery returns anything and <TT>FALSE</TT>
if the result set is empty. For example:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, ORDEREDON
FROM ORDERS 
WHERE EXISTS
(SELECT *
FROM ORDERS
WHERE NAME ='TRUE WHEEL')
</B>
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