?? ch06.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Day 6 -- Joining Tables</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="../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> <HR></CENTER><CENTER><H1><FONT COLOR="#000077">- Day 6 -<BR>Joining Tables</FONT></H1></CENTER><H2><FONT COLOR="#000077">Objectives</FONT></H2><P>Today you will learn about joins. This information will enable you to gather andmanipulate data across several tables. By the end of the day, you will understandand be able to do the following:<UL> <LI>Perform an outer join <P> <LI>Perform a left join <P> <LI>Perform a right join <P> <LI>Perform an equi-join <P> <LI>Perform a non-equi-join <P> <LI>Join a table to itself</UL><H2><FONT COLOR="#000077">Introduction</FONT></H2><P>One of the most powerful features of SQL is its capability to gather and manipulatedata from across several tables. Without this feature you would have to store allthe data elements necessary for each application in one table. Without common tablesyou would need to store the same data in several tables. Imagine having to redesign,rebuild, and repopulate your tables and databases every time your user needed a querywith a new piece of information. The <TT>JOIN</TT> statement of SQL enables you todesign smaller, more specific tables that are easier to maintain than larger tables.<H2><FONT COLOR="#000077">Multiple Tables in a Single SELECT Statement</FONT></H2><P>Like Dorothy in The Wizard of Oz, you have had the power to join tables sinceDay 2, "Introduction to the Query: The <TT>SELECT</TT> Statement," whenyou learned about <TT>SELECT</TT> and <TT>FROM</TT>. Unlike Dorothy, you don't haveto click you heels together three times to perform a join. Use the following twotables, named, cleverly enough, <TT>TABLE1</TT> and <TT>TABLE2</TT>.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The queries in today's examples were produced using Borland's ISQL tool. You will notice some differences between these queries and the ones that we used earlier in the book. For example, these queries do not begin with an SQL prompt. Another difference is that ISQL does not require a semicolon at the end of the statement. (The semicolon is optional in ISQL.) But the SQL basics are still the same. <HR></P></BLOCKQUOTE><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT *FROM TABLE1</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">ROW REMARKS========== =======row 1 Table 1row 2 Table 1row 3 Table 1row 4 Table 1row 5 Table 1row 6 Table 1</FONT></PRE><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF"><B>SELECT *FROM TABLE2</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">ROW REMARKS========== ========row 1 table 2row 2 table 2row 3 table 2row 4 table 2row 5 table 2row 6 table 2</FONT></PRE><P>To join these two tables, type this:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT *FROM TABLE1,TABLE2</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">ROW REMARKS ROW REMARKS========== ========== ========== ========row 1 Table 1 row 1 table 2row 1 Table 1 row 2 table 2row 1 Table 1 row 3 table 2row 1 Table 1 row 4 table 2row 1 Table 1 row 5 table 2row 1 Table 1 row 6 table 2row 2 Table 1 row 1 table 2row 2 Table 1 row 2 table 2row 2 Table 1 row 3 table 2row 2 Table 1 row 4 table 2row 2 Table 1 row 5 table 2row 2 Table 1 row 6 table 2row 3 Table 1 row 1 table 2row 3 Table 1 row 2 table 2row 3 Table 1 row 3 table 2row 3 Table 1 row 4 table 2row 3 Table 1 row 5 table 2row 3 Table 1 row 6 table 2row 4 Table 1 row 1 table 2row 4 Table 1 row 2 table 2row 4 Table 1 row 3 table 2row 4 Table 1 row 4 table 2row 4 Table 1 row 5 table 2row 4 Table 1 row 6 table 2row 5 Table 1 row 1 table 2row 5 Table 1 row 2 table 2row 5 Table 1 row 3 table 2row 5 Table 1 row 4 table 2row 5 Table 1 row 5 table 2row 5 Table 1 row 6 table 2row 6 Table 1 row 1 table 2row 6 Table 1 row 2 table 2row 6 Table 1 row 3 table 2row 6 Table 1 row 4 table 2row 6 Table 1 row 5 table 2row 6 Table 1 row 6 table 2</FONT></PRE><P>Thirty-six rows! Where did they come from? And what kind of join is this?</P><H5>ANALYSIS:</H5><P>A close examination of the result of your first join shows that each row from<TT>TABLE1</TT> was added to each row from <TT>TABLE2</TT>. An extract from thisjoin shows what happened:</P><H5>OUTPUT:</H5><PRE><FONT COLOR="#0066FF">ROW REMARKS ROW REMARKS===== ========== ========= ========row 1 Table 1 row 1 table 2row 1 Table 1 row 2 table 2row 1 Table 1 row 3 table 2row 1 Table 1 row 4 table 2row 1 Table 1 row 5 table 2row 1 Table 1 row 6 table 2</FONT></PRE><P>Notice how each row in <TT>TABLE2</TT> was combined with row 1 in <TT>TABLE1</TT>.Congratulations! You have performed your first join. But what kind of join? An innerjoin? an outer join? or what? Well, actually this type of join is called a cross-join.A cross-join is not normally as useful as the other joins covered today, but thisjoin does illustrate the basic combining property of all joins: Joins bring tablestogether.</P><P>Suppose you sold parts to bike shops for a living. When you designed your database,you built one big table with all the pertinent columns. Every time you had a newrequirement, you added a new column or started a new table with all the old dataplus the new data required to create a specific query. Eventually, your databasewould collapse from its own weight--not a pretty sight. An alternative design, basedon a relational model, would have you put all related data into one table. Here'show your customer table would look:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT *FROM CUSTOMER</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><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 NONELE SHOPPE HOMETOWN KS 54678 555-1278 NONEAAA BIKE 10 OLDTOWN NE 56784 555-3421 JOHN-MGRJACKS BIKE 24 EGLIN FL 34567 555-2314 NONE</FONT></PRE><H5>ANALYSIS:</H5><P>This table contains all the information you need to describe your customers. Theitems you sold would go into another table:</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><P>And the orders you take would have their own table:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT *FROM ORDERS</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF"> 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><P>One advantage of this approach is that you can have three specialized people ordepartments responsible for maintaining their own data. You don't need a databaseadministrator who is conversant with all aspects of your project to shepherd onegigantic, multidepartmental database. Another advantage is that in the age of networks,each table could reside on a different machine. People who understand the data couldmaintain it, and it could reside on an appropriate machine (rather than that nastycorporate mainframe protected by legions of system administrators).</P><P>Now join <TT>PARTS</TT> and <TT>ORDERS</TT>:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT O.ORDEREDON, O.NAME, O.PARTNUM,P.PARTNUM, P.DESCRIPTIONFROM ORDERS O, PART P</B> ORDEREDON NAME PARTNUM PARTNUM DESCRIPTION=========== ========== =========== ========= ============15-MAY-1996 TRUE WHEEL 23 54 PEDALS19-MAY-1996 TRUE WHEEL 76 54 PEDALS 2-SEP-1996 TRUE WHEEL 10 54 PEDALS30-JUN-1996 TRUE WHEEL 42 54 PEDALS30-JUN-1996 BIKE SPEC 54 54 PEDALS30-MAY-1996 BIKE SPEC 10 54 PEDALS30-MAY-1996 BIKE SPEC 23 54 PEDALS17-JAN-1996 BIKE SPEC 76 54 PEDALS17-JAN-1996 LE SHOPPE 76 54 PEDALS 1-JUN-1996 LE SHOPPE 10 54 PEDALS 1-JUN-1996 AAA BIKE 10 54 PEDALS 1-JUL-1996 AAA BIKE 76 54 PEDALS 1-JUL-1996 AAA BIKE 46 54 PEDALS11-JUL-1996 JACKS BIKE 76 54 PEDALS...</FONT></PRE><H5>ANALYSIS:</H5><P>The preceding code is just a portion of the result set. The actual set is 14 (numberof rows in <TT>ORDERS</TT>) x 6 (number of rows in <TT>PART</TT>), or 84 rows. Itis similar to the result from joining <TT>TABLE1</TT> and <TT>TABLE2</TT> earliertoday, and it is still one statement shy of being useful. Before we reveal that statement,we need to regress a little and talk about another use for the alias.<H3><FONT COLOR="#000077">Finding the Correct Column</FONT></H3><P>When you joined <TT>TABLE1</TT> and <TT>TABLE2</TT>, you used <TT>SELECT *</TT>,which returned all the columns in both tables. In joining <TT>ORDERS</TT> to <TT>PART</TT>,the <TT>SELECT</TT> statement is a bit more complicated:</P><PRE><FONT COLOR="#0066FF">SELECT O.ORDEREDON, O.NAME, O.PARTNUM,P.PARTNUM, P.DESCRIPTION</FONT></PRE><P>SQL is smart enough to know that <TT>ORDEREDON</TT> and <TT>NAME</TT> exist onlyin <TT>ORDERS</TT> and that <TT>DESCRIPTION</TT> exists only in <TT>PART</TT>, butwhat about <TT>PARTNUM</TT>, which exists in both? If you have a column that hasthe same name in two tables, you must use an alias in your <TT>SELECT</TT> clauseto specify which column you want to display. A common technique is to assign a singlecharacter to each table, as you did in the <TT>FROM</TT> clause:</P><PRE><FONT COLOR="#0066FF">FROM ORDERS O, PART P<B></B></FONT></PRE><P>You use that character with each column name, as you did in the preceding <TT>SELECT</TT>clause. The <TT>SELECT</TT> clause could also be written like this:</P><PRE><FONT COLOR="#0066FF">SELECT ORDEREDON, NAME, O.PARTNUM, P.PARTNUM, DESCRIPTION</FONT></PRE><P>But remember, someday you might have to come back and maintain this query. Itdoesn't hurt to make it more readable. Now back to the missing statement.<H2><FONT COLOR="#000077">Equi-Joins</FONT></H2><P>An extract from the <TT>PART/ORDERS</TT> join provides a clue as to what is missing:</P><PRE><FONT COLOR="#0066FF">30-JUN-1996 TRUE WHEEL 42 54 PEDALS30-JUN-1996 BIKE SPEC 54 54 PEDALS30-MAY-1996 BIKE SPEC 10 54 PEDALS</FONT></PRE><P>Notice the <TT>PARTNUM</TT> fields that are common to both tables. What if youwrote the following?</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT O.ORDEREDON, O.NAME, O.PARTNUM,P.PARTNUM, P.DESCRIPTIONFROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUM</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#000000"></FONT></PRE><PRE><FONT COLOR="#0066FF"> ORDEREDON NAME PARTNUM PARTNUM DESCRIPTION=========== ========== =========== ========= ============== 1-JUN-1996 AAA BIKE 10 10 TANDEM30-MAY-1996 BIKE SPEC 10 10 TANDEM 2-SEP-1996 TRUE WHEEL 10 10 TANDEM 1-JUN-1996 LE SHOPPE 10 10 TANDEM30-MAY-1996 BIKE SPEC 23 23 MOUNTAIN BIKE15-MAY-1996 TRUE WHEEL 23 23 MOUNTAIN BIKE30-JUN-1996 TRUE WHEEL 42 42 SEATS 1-JUL-1996 AAA BIKE 46 46 TIRES30-JUN-1996 BIKE SPEC 54 54 PEDALS 1-JUL-1996 AAA BIKE 76 76 ROAD BIKE17-JAN-1996 BIKE SPEC 76 76 ROAD BIKE19-MAY-1996 TRUE WHEEL 76 76 ROAD BIKE11-JUL-1996 JACKS BIKE 76 76 ROAD BIKE17-JAN-1996 LE SHOPPE 76 76 ROAD BIKE</FONT></PRE><H5>ANALYSIS:</H5><P>Using the column <TT>PARTNUM</TT> that exists in both of the preceding tables,you have just combined the information you had stored in the <TT>ORDERS</TT> tablewith information from the <TT>PART</TT> table to show a description of the parts?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -