?? ch07.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 7 -- Subqueries: The Embedded SELECT Statement</TITLE>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="sams.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="ch06.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch06.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="wk1rev.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/wk1rev.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 7 -<BR>
Subqueries: The Embedded SELECT Statement</FONT></H1>
</CENTER>
<H2><FONT COLOR="#000077">Objectives</FONT></H2>
<P>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:
<UL>
<LI>Build a subquery
<P>
<LI>Use the keywords <TT>EXISTS</TT>, <TT>ANY</TT>, and <TT>ALL</TT> with your subqueries
<P>
<LI>Build and use correlated subqueries
</UL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>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.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077">Building a Subquery</FONT></H2>
<P>Simply put, a subquery lets you tie the result set of one query to another. The
general syntax is as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">SELECT *
FROM TABLE1
WHERE TABLE1.SOMECOLUMN =
(SELECT SOMEOTHERCOLUMN
FROM TABLE2
WHERE SOMEOTHERCOLUMN = SOMEVALUE)
</FONT></PRE>
<P>Notice how the second query is nested inside the first. Here's a real-world example
that uses the <TT>PART</TT> and <TT>ORDERS</TT> tables:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT *
FROM PART</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> 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</FONT></PRE>
<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>
<H5>ANALYSIS:</H5>
<P>The tables share a common field called <TT>PARTNUM</TT>. Suppose that you didn't
know (or didn't want to know) the <TT>PARTNUM</TT>, but instead wanted to work with
the description of the part. Using a subquery, you could type this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT *
FROM ORDERS
WHERE PARTNUM =
(SELECT PARTNUM
FROM PART
WHERE DESCRIPTION LIKE "ROAD%")
</B>
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
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Even better, if you use the concepts you learned on Day 6, you could enhance the
<TT>PARTNUM</TT> column in the result by including the <TT>DESCRIPTION</TT>, making
<TT>PARTNUM</TT> clearer for anyone who hasn't memorized it. Try this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>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%")</B>
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
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The first part of the query is very familiar:</P>
<PRE><FONT COLOR="#0066FF">SELECT O.ORDEREDON, O.PARTNUM,
P.DESCRIPTION, O.QUANTITY, O.REMARKS
FROM ORDERS O, PART P
</FONT></PRE>
<P>Here you are using the aliases <TT>O</TT> and <TT>P</TT> for tables <TT>ORDERS</TT>
and <TT>PART</TT> 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 <TT>WHERE</TT> clause you encounter</P>
<PRE><FONT COLOR="#0066FF">WHERE O.PARTNUM = P.PARTNUM
</FONT></PRE>
<P>is standard language for the join of tables <TT>PART</TT> and <TT>ORDERS</TT>
specified in the <TT>FROM</TT> clause. If you didn't use this <TT>WHERE</TT> clause,
you would have all the possible row combinations of the two tables. The next section
includes the subquery. The statement</P>
<PRE><FONT COLOR="#0066FF">AND
O.PARTNUM =
(SELECT PARTNUM
FROM PART
WHERE DESCRIPTION LIKE "ROAD%")
</FONT></PRE>
<P>adds the qualification that <TT>O.PARTNUM</TT> must be equal to the result of
your simple subquery. The subquery is straightforward, finding all the part numbers
that are <TT>LIKE "ROAD%"</TT>. The use of <TT>LIKE</TT> was somewhat lazy,
saving you the keystrokes required to type <TT>ROAD BIKE</TT>. However, it turns
out you were lucky this time. What if someone in the Parts department had added a
new part called <TT>ROADKILL</TT>? The revised <TT>PART</TT> table would look like
this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT *
FROM PART</B>
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
</FONT></PRE>
<P>Suppose you are blissfully unaware of this change and try your query after this
new product was added. If you enter this:</P>
<PRE><FONT COLOR="#0066FF"><B>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%")</B>
</FONT></PRE>
<P>the SQL engine complains</P>
<PRE><FONT COLOR="#0066FF">multiple rows in singleton select
</FONT></PRE>
<P>and you don't get any results. The response from your SQL engine may vary, but
it still complains and returns nothing.</P>
<P>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:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PARTNUM
FROM PART
WHERE DESCRIPTION LIKE "ROAD%"
</B>
PARTNUM
===========
76
77
</FONT></PRE>
<P>You would take this result and apply it to <TT>O.PARTNUM =</TT>, which is the
step that causes the problem.</P>
<H5>ANALYSIS:</H5>
<P>How can <TT>PARTNUM</TT> be equal to both <TT>76</TT> and <TT>77</TT>? This must
be what the engine meant when it accused you of being a simpleton. When you used
the <TT>LIKE</TT> clause, you opened yourself up for this error. When you combine
the results of a relational operator with another relational operator, such as <TT>=</TT>,
<TT><</TT>, or <TT>></TT>, 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 <TT>=</TT> instead of the <TT>LIKE</TT>, like this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>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")</B>
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
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This subquery returns only one unique result; therefore narrowing your <TT>=</TT>
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?</P>
<P>Avoiding the use of <TT>LIKE</TT> 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.
<H2><FONT COLOR="#000077">Using Aggregate Functions with Subqueries</FONT></H2>
<P>The aggregate functions <TT>SUM</TT>, <TT>COUNT</TT>, <TT>MIN</TT>, <TT>MAX</TT>,
and <TT>AVG</TT> all return a single value. To find the average amount of an order,
type this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#000000"><B></B></FONT></PRE>
<PRE><FONT COLOR="#0066FF"> AVG
===========
2419.16
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This statement returns only one value. To find out which orders were above average,
use the preceding <TT>SELECT</TT> statement for your subquery. The complete query
and result are as follows:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>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)</B>
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
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This example contains a rather unremarkable <TT>SELECT/FROM/WHERE</TT> clause:</P>
<PRE><FONT COLOR="#0066FF">SELECT O.NAME, O.ORDEREDON,
O.QUANTITY * P.PRICE TOTAL
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
</FONT></PRE>
<P>These lines represent the common way of joining these two tables. This join is
necessary because the price is in <TT>PART</TT> and the quantity is in <TT>ORDERS</TT>.
The <TT>WHERE</TT> ensures that you examine only the join-formed rows that are related.
You then add the subquery:</P>
<PRE><FONT COLOR="#0066FF">AND
O.QUANTITY * P.PRICE >
(SELECT AVG(O.QUANTITY * P.PRICE)
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM)
</FONT></PRE>
<P>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 <TT>SELECT</TT> 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.
<H2><FONT COLOR="#000077">Nested Subqueries</FONT></H2>
<P>Nesting is the act of embedding a subquery within another subquery. For example:</P>
<PRE><FONT COLOR="#0066FF">Select * FROM SOMETHING WHERE ( SUBQUERY(SUBQUERY(SUBQUERY)));
</FONT></PRE>
<P>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 <TT>CUSTOMER</TT></P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT *
FROM CUSTOMER</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#000000"><B></B></FONT></PRE>
<PRE><FONT COLOR="#0066FF">NAME ADDRESS STATE ZIP PHONE REMARKS
========== ========== ====== ========== =========== ==========
TRUE WHEEL 55O HUSKER NE 58702 555-4545 NONE
BIKE SPEC CPT SHRIVE LA 45678 555-1234 NONE
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -