?? ch07.htm
字號(hào):
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 >
(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 >
(...)
</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 >
(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, "Introduction to the Query: The <TT>SELECT</TT> Statement."
<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) >
(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) >
(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 "ROAD BIKE"
</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) >
(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) >
</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
?? 快捷鍵說(shuō)明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -