?? ch06.htm
字號:
76 ROAD BIKE 530.00 BIKE SPEC 54 10 TANDEM 1200.00 BIKE SPEC 54</FONT></PRE><BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The syntax you used to get this join--<TT>JOIN</TT> <TT>ON</TT>--is not ANSI standard. The implementation you used for this example has additional syntax. You are using it here to specify an inner and an outer join. Most implementations of SQL have similar extensions. Notice the absence of the <TT>WHERE</TT> clause in this type of join. <HR></BLOCKQUOTE><H5>ANALYSIS:</H5><P>The result is that all the rows in <TT>PART</TT> are spliced on to specific rowsin <TT>ORDERS</TT> where the column <TT>PARTNUM</TT> is <TT>54</TT>. Here's a <TT>RIGHTOUTER JOIN </TT>statement:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE,O.NAME, O.PARTNUMFROM PART PRIGHT OUTER JOIN ORDERS O ON ORDERS.PARTNUM = 54</B>PARTNUM DESCRIPTION PRICE NAME PARTNUM======= ==================== ======= ============== ======= <null> <null> <null> TRUE WHEEL 23 <null> <null> <null> TRUE WHEEL 76 <null> <null> <null> TRUE WHEEL 10 <null> <null> <null> TRUE WHEEL 42 54 PEDALS 54.25 BIKE SPEC 54 42 SEATS 24.50 BIKE SPEC 54 46 TIRES 15.25 BIKE SPEC 54 23 MOUNTAIN BIKE 350.45 BIKE SPEC 54 76 ROAD BIKE 530.00 BIKE SPEC 54 10 TANDEM 1200.00 BIKE SPEC 54 <null> <null> <null> BIKE SPEC 10 <null> <null> <null> BIKE SPEC 23 <null> <null> <null> BIKE SPEC 76 <null> <null> <null> LE SHOPPE 76 <null> <null> <null> LE SHOPPE 10 <null> <null> <null> AAA BIKE 10 <null> <null> <null> AAA BIKE 76 <null> <null> <null> AAA BIKE 46 <null> <null> <null> JACKS BIKE 76</FONT></PRE><H5>ANALYSIS:</H5><P>This type of query is new. First you specified a <TT>RIGHT OUTER JOIN</TT>, whichcaused SQL to return a full set of the right table, <TT>ORDERS</TT>, and to placenulls in the fields where <TT>ORDERS.PARTNUM <> 54</TT>. Following is a <TT>LEFTOUTER JOIN</TT> statement:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE,O.NAME, O.PARTNUMFROM PART PLEFT OUTER JOIN ORDERS O ON ORDERS.PARTNUM = 54</B>PARTNUM DESCRIPTION PRICE NAME PARTNUM======= ================== =========== ========== =========== 54 PEDALS 54.25 BIKE SPEC 54 42 SEATS 24.50 BIKE SPEC 54 46 TIRES 15.25 BIKE SPEC 54 23 MOUNTAIN BIKE 350.45 BIKE SPEC 54 76 ROAD BIKE 530.00 BIKE SPEC 54 10 TANDEM 1200.00 BIKE SPEC 54</FONT></PRE><H5>ANALYSIS:</H5><P>You get the same six rows as the <TT>INNER JOIN</TT>. Because you specified <TT>LEFT</TT>(the <TT>LEFT</TT> table), <TT>PART</TT> determined the number of rows you wouldreturn. Because <TT>PART</TT> is smaller than <TT>ORDERS</TT>, SQL saw no need topad those other fields with blanks.</P><P>Don't worry too much about inner and outer joins. Most SQL products determinethe optimum <TT>JOIN</TT> for your query. In fact, if you are placing your queryinto a stored procedure (or using it inside a program (both stored procedures andEmbedded SQL covered on Day 13, "Advanced SQL Topics"), you should notspecify a join type even if your SQL implementation provides the proper syntax. Ifyou do specify a join type, the optimizer chooses your way instead of the optimumway.</P><P>Some implementations of SQL use the <TT>+</TT> sign instead of an <TT>OUTER</TT><TT>JOIN</TT> statement. The <TT>+</TT> simply means "Show me everything evenif something is missing." Here's the syntax:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SQL> select e.name, e.employee_id, ep.salary, ep.marital_status from e,ployee_tbl e, employee_pay_tbl ep where e.employee_id = ep.employee_id(+) and e.name like '%MITH';</FONT></PRE><H5>ANALYSIS:</H5><P>This statement is joining the two tables. The <TT>+</TT> sign on the <TT>ep.employee_id</TT>column will return all rows even if they are empty.<H2><FONT COLOR="#000077">Joining a Table to Itself</FONT></H2><P>Today's final topic is the often-used technique of joining a table to itself.The syntax of this operation is similar to joining two tables. For example, to jointable <TT>TABLE1</TT> to itself, type this:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT *FROM TABLE1, TABLE1</B></FONT></PRE><H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5><PRE><FONT COLOR="#000000"><B></B></FONT></PRE><PRE><FONT COLOR="#0066FF">ROW REMARKS ROW REMARKS========== ========== ========== ========row 1 Table 1 row 1 Table 1row 1 Table 1 row 2 Table 1row 1 Table 1 row 3 Table 1row 1 Table 1 row 4 Table 1row 1 Table 1 row 5 Table 1row 1 Table 1 row 6 Table 1row 2 Table 1 row 1 Table 1row 2 Table 1 row 2 Table 1row 2 Table 1 row 3 Table 1row 2 Table 1 row 4 Table 1row 2 Table 1 row 5 Table 1row 2 Table 1 row 6 Table 1row 3 Table 1 row 1 Table 1row 3 Table 1 row 2 Table 1row 3 Table 1 row 3 Table 1row 3 Table 1 row 4 Table 1row 3 Table 1 row 5 Table 1row 3 Table 1 row 6 Table 1row 4 Table 1 row 1 Table 1row 4 Table 1 row 2 Table 1...</FONT></PRE><H5>ANALYSIS:</H5><P>In its complete form, this join produces the same number of combinations as joiningtwo 6-row tables. This type of join could be useful to check the internal consistencyof data. What would happen if someone fell asleep in the production department andentered a new part with a <TT>PARTNUM</TT> that already existed? That would be badnews for everybody: Invoices would be wrong; your application would probably blowup; and in general you would be in for a very bad time. And the cause of all yourproblems would be the duplicate <TT>PARTNUM</TT> in the following table:</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 76 CLIPPLESS SHOE 65.00 <-NOTE SAME #</FONT></PRE><P>You saved your company from this bad situation by checking <TT>PART</TT> beforeanyone used it:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT F.PARTNUM, F.DESCRIPTION,S.PARTNUM,S.DESCRIPTIONFROM PART F, PART SWHERE F.PARTNUM = S.PARTNUMAND F.DESCRIPTION <> S.DESCRIPTION</B> PARTNUM DESCRIPTION PARTNUM DESCRIPTION========== ======================== ======= ============ 76 ROAD BIKE 76 CLIPPLESS SHOE 76 CLIPPLESS SHOE 76 ROAD BIKE</FONT></PRE><H5>ANALYSIS:</H5><P>Now you are a hero until someone asks why the table has only two entries. You,remembering what you have learned about <TT>JOIN</TT>s, retain your hero status byexplaining how the join produced two rows that satisfied the condition <TT>WHEREF.PARTNUM = S.PARTNUM AND F.DESCRIPTION <> S.DESCRIPTION</TT>. Of course, atsome point, the row of data containing the duplicate <TT>PARTNUM</TT> would haveto be corrected.<H2><FONT COLOR="#000077">Summary</FONT></H2><P>Today you learned that a join combines all possible combinations of rows presentin the selected tables. These new rows are then available for selection based onthe information that you want.</P><P>Congratulations--you have learned almost everything there is to know about the<TT>SELECT</TT> clause. The one remaining item, subqueries, is covered tomorrow (Day7, "Subqueries: The Embedded <TT>SELECT</TT> Statement").<H2><FONT COLOR="#000077">Q&A</FONT></H2><DL> <DD><B>Q Why cover outer, inner, left, and right joins when I probably won't ever use them?</B> <P><B>A</B> A little knowledge is a dangerous thing, and no knowledge can be expensive. You now know enough to understand the basics of what your SQL engine might try while optimizing you queries.</P> <P><B>Q How many tables can you join on?</B></P> <P><B>A </B>That depends on the implementation. Some implementations have a 25-table limit, whereas others have no limit. Just remember, the more tables you join on, the slower the response time will be. To be safe, check your implementation to find out the maximum number of tables allowed in a query.</P> <P><B>Q Would it be fair to say that when tables are joined, they actually become one table?</B></P> <P><B>A </B>Very simply put, that is just about what happens. When you join the tables, you can select from any of the columns in either table.</DL><H2><FONT COLOR="#000077">Workshop</FONT></H2><P>The Workshop provides quiz questions to help solidify your understanding of thematerial covered, as well as exercises to provide you with experience in using whatyou have learned. Try to answer the quiz and exercise questions before checking theanswers in Appendix F, "Answers to Quizzes and Exercises."<H3><FONT COLOR="#000077">Quiz</FONT></H3><DL> <DD><B>1.</B> How many rows would a two-table join produce if one table had 50,000 rows and the other had 100,000? <P><B>2.</B> What type of join appears in the following <TT>SELECT</TT> statement?</DL><BLOCKQUOTE> <PRE><FONT COLOR="#0066FF"> select e.name, e.employee_id, ep.salary from employee_tbl e, employee_pay_tbl ep where e.employee_id = ep.employee_id;</FONT></PRE></BLOCKQUOTE><DL> <DD><B>3.</B> Will the following <TT>SELECT</TT> statements work?</DL><BLOCKQUOTE> <BLOCKQUOTE> <P><FONT COLOR="#000000">a. </FONT><FONT COLOR="#0066FF"><TT>select name, employee_id, salary <BR> from employee_tbl e, <BR> employee_pay_tbl ep <BR> where employee_id = employee_id <BR> and name like '%MITH';<BR> </TT></FONT></P> <P><FONT COLOR="#000000">b. </FONT><FONT COLOR="#0066FF"><TT>select e.name, e.employee_id, ep.salary <BR> from employee_tbl e, <BR> employee_pay_tbl ep <BR> where name like '%MITH';<BR> </TT></FONT></P> <P><FONT COLOR="#000000">c. </FONT><FONT COLOR="#0066FF"><TT>select e.name, e.employee_id, ep.salary <BR> from employee_tbl e, <BR> employee_pay_tbl ep <BR> where e.employee_id = ep.employee_id <BR> and e.name like '%MITH';</TT></FONT></P> </BLOCKQUOTE></BLOCKQUOTE><DL> <DD><B>4. </B>In the <TT>WHERE</TT> clause, when joining the tables, should you do the join first or the conditions? <P><B>5.</B> In joining tables are you limited to one-column joins, or can you join on more than one column?</DL><H3><FONT COLOR="#000077">Exercises</FONT></H3><DL> <DD><B>1. </B>In the section on joining tables to themselves, the last example returned two combinations. Rewrite the query so only one entry comes up for each redundant part number. <P><B>2. </B>Rewrite the following query to make it more readable and shorter.</P> <H5>INPUT:</H5></DL><PRE><FONT COLOR="#0066FF"> <B>select orders.orderedon, orders.name, part.partnum, part.price, part.description from orders, part where orders.partnum = part.partnum and orders.orderedon between '1-SEP-96' and '30-SEP-96' order by part.partnum;</B></FONT></PRE><DL> <DD><B>3. </B>From the <TT>PART</TT> table and the <TT>ORDERS</TT> table, make up a query that will return the following: <H5>OUTPUT:</H5></DL><PRE></PRE><BLOCKQUOTE> <PRE><FONT COLOR="#0066FF">ORDEREDON NAME PARTNUM QUANTITY================== ================== ======= ========2-SEP-96 TRUE WHEEL 10 1</FONT></PRE></BLOCKQUOTE><CENTER><P><HR><A HREF="../ch05/ch05.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch07/ch07.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><BR><BR><BR><IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"BORDER="0"></P><P>© <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. Allrights reserved.</CENTER></BODY></HTML>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -