📄 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 rows
in <TT>ORDERS</TT> where the column <TT>PARTNUM</TT> is <TT>54</TT>. Here's a <TT>RIGHT
OUTER JOIN </TT>statement:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE,
O.NAME, O.PARTNUM
FROM PART P
RIGHT 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>, which
caused SQL to return a full set of the right table, <TT>ORDERS</TT>, and to place
nulls in the fields where <TT>ORDERS.PARTNUM <> 54</TT>. Following is a <TT>LEFT
OUTER JOIN</TT> statement:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE,
O.NAME, O.PARTNUM
FROM PART P
LEFT 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 would
return. Because <TT>PART</TT> is smaller than <TT>ORDERS</TT>, SQL saw no need to
pad those other fields with blanks.</P>
<P>Don't worry too much about inner and outer joins. Most SQL products determine
the optimum <TT>JOIN</TT> for your query. In fact, if you are placing your query
into a stored procedure (or using it inside a program (both stored procedures and
Embedded SQL covered on Day 13, "Advanced SQL Topics"), you should not
specify a join type even if your SQL implementation provides the proper syntax. If
you do specify a join type, the optimizer chooses your way instead of the optimum
way.</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 even
if 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 join
table <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 1
row 1 Table 1 row 2 Table 1
row 1 Table 1 row 3 Table 1
row 1 Table 1 row 4 Table 1
row 1 Table 1 row 5 Table 1
row 1 Table 1 row 6 Table 1
row 2 Table 1 row 1 Table 1
row 2 Table 1 row 2 Table 1
row 2 Table 1 row 3 Table 1
row 2 Table 1 row 4 Table 1
row 2 Table 1 row 5 Table 1
row 2 Table 1 row 6 Table 1
row 3 Table 1 row 1 Table 1
row 3 Table 1 row 2 Table 1
row 3 Table 1 row 3 Table 1
row 3 Table 1 row 4 Table 1
row 3 Table 1 row 5 Table 1
row 3 Table 1 row 6 Table 1
row 4 Table 1 row 1 Table 1
row 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 joining
two 6-row tables. This type of join could be useful to check the internal consistency
of data. What would happen if someone fell asleep in the production department and
entered a new part with a <TT>PARTNUM</TT> that already existed? That would be bad
news for everybody: Invoices would be wrong; your application would probably blow
up; and in general you would be in for a very bad time. And the cause of all your
problems 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> before
anyone used it:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT F.PARTNUM, F.DESCRIPTION,
S.PARTNUM,S.DESCRIPTION
FROM PART F, PART S
WHERE F.PARTNUM = S.PARTNUM
AND 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 by
explaining how the join produced two rows that satisfied the condition <TT>WHERE
F.PARTNUM = S.PARTNUM AND F.DESCRIPTION <> S.DESCRIPTION</TT>. Of course, at
some point, the row of data containing the duplicate <TT>PARTNUM</TT> would have
to be corrected.
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>Today you learned that a join combines all possible combinations of rows present
in the selected tables. These new rows are then available for selection based on
the 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 (Day
7, "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 the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers 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.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><BR>
<BR>
<BR>
<IMG SRC="corp.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>
<P>© <A HREF="copy.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>
</BODY>
</HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -