ch06.htm

来自「Learn SQL in an easy way...」· HTM 代码 · 共 977 行 · 第 1/3 页

     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
======= ==================== ======= ============== =======

 &lt;null&gt; &lt;null&gt;                &lt;null&gt; TRUE WHEEL          23
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; TRUE WHEEL          76
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; TRUE WHEEL          10
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; 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
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; BIKE SPEC           10
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; BIKE SPEC           23
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; BIKE SPEC           76
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; LE SHOPPE           76
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; LE SHOPPE           10
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; AAA BIKE            10
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; AAA BIKE            76
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; AAA BIKE            46
 &lt;null&gt; &lt;null&gt;                &lt;null&gt; 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 &lt;&gt; 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, &quot;Advanced SQL Topics&quot;), 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 &quot;Show me everything even
if something is missing.&quot; Here's the syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; 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 &lt;-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 &lt;&gt; 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 &lt;&gt; 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, &quot;Subqueries: The Embedded <TT>SELECT</TT> Statement&quot;).
<H2><FONT COLOR="#000077">Q&amp;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, &quot;Answers to Quizzes and Exercises.&quot;
<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"><IMG
SRC="../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>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>