?? 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="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="ch05.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch05.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="ch07.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch07.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 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 and
manipulate data across several tables. By the end of the day, you will understand
and 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 manipulate
data from across several tables. Without this feature you would have to store all
the data elements necessary for each application in one table. Without common tables
you 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 query
with a new piece of information. The <TT>JOIN</TT> statement of SQL enables you to
design 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 since
Day 2, "Introduction to the Query: The <TT>SELECT</TT> Statement," when
you learned about <TT>SELECT</TT> and <TT>FROM</TT>. Unlike Dorothy, you don't have
to click you heels together three times to perform a join. Use the following two
tables, 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 1
row 2 Table 1
row 3 Table 1
row 4 Table 1
row 5 Table 1
row 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 2
row 2 table 2
row 3 table 2
row 4 table 2
row 5 table 2
row 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 2
row 1 Table 1 row 2 table 2
row 1 Table 1 row 3 table 2
row 1 Table 1 row 4 table 2
row 1 Table 1 row 5 table 2
row 1 Table 1 row 6 table 2
row 2 Table 1 row 1 table 2
row 2 Table 1 row 2 table 2
row 2 Table 1 row 3 table 2
row 2 Table 1 row 4 table 2
row 2 Table 1 row 5 table 2
row 2 Table 1 row 6 table 2
row 3 Table 1 row 1 table 2
row 3 Table 1 row 2 table 2
row 3 Table 1 row 3 table 2
row 3 Table 1 row 4 table 2
row 3 Table 1 row 5 table 2
row 3 Table 1 row 6 table 2
row 4 Table 1 row 1 table 2
row 4 Table 1 row 2 table 2
row 4 Table 1 row 3 table 2
row 4 Table 1 row 4 table 2
row 4 Table 1 row 5 table 2
row 4 Table 1 row 6 table 2
row 5 Table 1 row 1 table 2
row 5 Table 1 row 2 table 2
row 5 Table 1 row 3 table 2
row 5 Table 1 row 4 table 2
row 5 Table 1 row 5 table 2
row 5 Table 1 row 6 table 2
row 6 Table 1 row 1 table 2
row 6 Table 1 row 2 table 2
row 6 Table 1 row 3 table 2
row 6 Table 1 row 4 table 2
row 6 Table 1 row 5 table 2
row 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 this
join shows what happened:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">ROW REMARKS ROW REMARKS
===== ========== ========= ========
row 1 Table 1 row 1 table 2
row 1 Table 1 row 2 table 2
row 1 Table 1 row 3 table 2
row 1 Table 1 row 4 table 2
row 1 Table 1 row 5 table 2
row 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 inner
join? 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 this
join does illustrate the basic combining property of all joins: Joins bring tables
together.</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 new
requirement, you added a new column or started a new table with all the old data
plus the new data required to create a specific query. Eventually, your database
would collapse from its own weight--not a pretty sight. An alternative design, based
on a relational model, would have you put all related data into one table. Here's
how 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 NONE
BIKE SPEC CPT SHRIVE LA 45678 555-1234 NONE
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>
<H5>ANALYSIS:</H5>
<P>This table contains all the information you need to describe your customers. The
items 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 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>One advantage of this approach is that you can have three specialized people or
departments responsible for maintaining their own data. You don't need a database
administrator who is conversant with all aspects of your project to shepherd one
gigantic, multidepartmental database. Another advantage is that in the age of networks,
each table could reside on a different machine. People who understand the data could
maintain it, and it could reside on an appropriate machine (rather than that nasty
corporate 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.DESCRIPTION
FROM ORDERS O, PART P</B>
ORDEREDON NAME PARTNUM PARTNUM DESCRIPTION
=========== ========== =========== ========= ============
15-MAY-1996 TRUE WHEEL 23 54 PEDALS
19-MAY-1996 TRUE WHEEL 76 54 PEDALS
2-SEP-1996 TRUE WHEEL 10 54 PEDALS
30-JUN-1996 TRUE WHEEL 42 54 PEDALS
30-JUN-1996 BIKE SPEC 54 54 PEDALS
30-MAY-1996 BIKE SPEC 10 54 PEDALS
30-MAY-1996 BIKE SPEC 23 54 PEDALS
17-JAN-1996 BIKE SPEC 76 54 PEDALS
17-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 PEDALS
11-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 (number
of rows in <TT>ORDERS</TT>) x 6 (number of rows in <TT>PART</TT>), or 84 rows. It
is similar to the result from joining <TT>TABLE1</TT> and <TT>TABLE2</TT> earlier
today, 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 only
in <TT>ORDERS</TT> and that <TT>DESCRIPTION</TT> exists only in <TT>PART</TT>, but
what about <TT>PARTNUM</TT>, which exists in both? If you have a column that has
the same name in two tables, you must use an alias in your <TT>SELECT</TT> clause
to specify which column you want to display. A common technique is to assign a single
character 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. It
doesn'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 PEDALS
30-JUN-1996 BIKE SPEC 54 54 PEDALS
30-MAY-1996 BIKE SPEC 10 54 PEDALS
</FONT></PRE>
<P>Notice the <TT>PARTNUM</TT> fields that are common to both tables. What if you
wrote the following?</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT O.ORDEREDON, O.NAME, O.PARTNUM,
P.PARTNUM, P.DESCRIPTION
FROM ORDERS O, PART P
WHERE 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 TANDEM
30-MAY-1996 BIKE SPEC 10 10 TANDEM
2-SEP-1996 TRUE WHEEL 10 10 TANDEM
1-JUN-1996 LE SHOPPE 10 10 TANDEM
30-MAY-1996 BIKE SPEC 23 23 MOUNTAIN BIKE
15-MAY-1996 TRUE WHEEL 23 23 MOUNTAIN BIKE
30-JUN-1996 TRUE WHEEL 42 42 SEATS
1-JUL-1996 AAA BIKE 46 46 TIRES
30-JUN-1996 BIKE SPEC 54 54 PEDALS
1-JUL-1996 AAA BIKE 76 76 ROAD BIKE
17-JAN-1996 BIKE SPEC 76 76 ROAD BIKE
19-MAY-1996 TRUE WHEEL 76 76 ROAD BIKE
11-JUL-1996 JACKS BIKE 76 76 ROAD BIKE
17-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> table
with information from the <TT>PART</TT> table to show a description of the parts
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -