?? ch07.htm
字號(hào):
<!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></HEAD><BODY TEXT="#000000" BGCOLOR="#FFFFFF"><CENTER><H1><IMG SRC="../buttonart/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/ch06.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../wk1rev/wk1rev.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/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, youwill 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. Thegeneral syntax is as follows:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SELECT *FROM TABLE1WHERE TABLE1.SOMECOLUMN =(SELECT SOMEOTHERCOLUMNFROM TABLE2WHERE SOMEOTHERCOLUMN = SOMEVALUE)</FONT></PRE><P>Notice how the second query is nested inside the first. Here's a real-world examplethat 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 PAID19-MAY-1996 TRUE WHEEL 76 3 PAID 2-SEP-1996 TRUE WHEEL 10 1 PAID30-JUN-1996 TRUE WHEEL 42 8 PAID30-JUN-1996 BIKE SPEC 54 10 PAID30-MAY-1996 BIKE SPEC 10 2 PAID30-MAY-1996 BIKE SPEC 23 8 PAID17-JAN-1996 BIKE SPEC 76 11 PAID17-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 PAID11-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'tknow (or didn't want to know) the <TT>PARTNUM</TT>, but instead wanted to work withthe description of the part. Using a subquery, you could type this:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT *FROM ORDERSWHERE PARTNUM =(SELECT PARTNUMFROM PARTWHERE DESCRIPTION LIKE "ROAD%")</B> ORDEREDON NAME PARTNUM QUANTITY REMARKS=========== ========== =========== =========== ========19-MAY-1996 TRUE WHEEL 76 3 PAID17-JAN-1996 BIKE SPEC 76 11 PAID17-JAN-1996 LE SHOPPE 76 5 PAID 1-JUL-1996 AAA BIKE 76 4 PAID11-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.REMARKSFROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUMANDO.PARTNUM =(SELECT PARTNUMFROM PARTWHERE DESCRIPTION LIKE "ROAD%")</B> ORDEREDON PARTNUM DESCRIPTION QUANTITY REMARKS=========== =========== ============ =========== =========19-MAY-1996 76 ROAD BIKE 3 PAID 1-JUL-1996 76 ROAD BIKE 4 PAID17-JAN-1996 76 ROAD BIKE 5 PAID17-JAN-1996 76 ROAD BIKE 11 PAID11-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.REMARKSFROM 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 casethe aliases were not necessary because each of the columns you asked to return isunique. However, it is easier to make a readable query now than to have to figureit 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 sectionincludes the subquery. The statement</P><PRE><FONT COLOR="#0066FF">ANDO.PARTNUM =(SELECT PARTNUMFROM PARTWHERE DESCRIPTION LIKE "ROAD%")</FONT></PRE><P>adds the qualification that <TT>O.PARTNUM</TT> must be equal to the result ofyour simple subquery. The subquery is straightforward, finding all the part numbersthat 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 turnsout you were lucky this time. What if someone in the Parts department had added anew part called <TT>ROADKILL</TT>? The revised <TT>PART</TT> table would look likethis:</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 thisnew product was added. If you enter this:</P><PRE><FONT COLOR="#0066FF"><B>SELECT O.ORDEREDON, O.PARTNUM,P.DESCRIPTION, O.QUANTITY, O.REMARKSFROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUMANDO.PARTNUM =(SELECT PARTNUMFROM PARTWHERE 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, butit 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 PARTNUMFROM PARTWHERE 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 thestep 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 mustbe what the engine meant when it accused you of being a simpleton. When you usedthe <TT>LIKE</TT> clause, you opened yourself up for this error. When you combinethe 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. Inthe case of the example we have been using, the solution would be to rewrite thequery 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.REMARKSFROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUMANDO.PARTNUM =(SELECT PARTNUMFROM PARTWHERE DESCRIPTION = "ROAD BIKE")</B> ORDEREDON PARTNUM DESCRIPTION QUANTITY REMARKS=========== =========== =============== =========== ==========19-MAY-1996 76 ROAD BIKE 3 PAID 1-JUL-1996 76 ROAD BIKE 4 PAID17-JAN-1996 76 ROAD BIKE 5 PAID17-JAN-1996 76 ROAD BIKE 11 PAID11-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 multiplevalues 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 theuniqueness 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 checka given field for uniqueness. If you design the table yourself (see Day 9, "Creatingand Maintaining Tables") or trust the person who designed the table, you couldrequire the column you are searching to have a unique value. You could also use apart 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 PWHERE 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 queryand result are as follows:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT O.NAME, O.ORDEREDON,O.QUANTITY * P.PRICE TOTALFROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUMANDO.QUANTITY * P.PRICE >(SELECT AVG(O.QUANTITY * P.PRICE)FROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUM)</B>NAME ORDEREDON TOTAL========== =========== ===========LE SHOPPE 1-JUN-1996 3600.00BIKE SPEC 30-MAY-1996 2803.60LE SHOPPE 17-JAN-1996 2650.00BIKE SPEC 17-JAN-1996 5830.00JACKS 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 TOTALFROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUM</FONT></PRE><P>These lines represent the common way of joining these two tables. This join isnecessary 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">ANDO.QUANTITY * P.PRICE >(SELECT AVG(O.QUANTITY * P.PRICE)FROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUM)</FONT></PRE><P>The preceding condition compares the total of each order with the average youcomputed in the subquery. Note that the join in the subquery is required for thesame reasons as in the main <TT>SELECT</TT> statement. This join is also constructedexactly the same way. There are no secret handshakes in subqueries; they have exactlythe same syntax as a standalone query. In fact, most subqueries start out as standalonequeries 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 ofmoney, 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 NONEBIKE SPEC CPT SHRIVE LA 45678 555-1234 NONE?? 快捷鍵說(shuō)明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -