ch06.htm

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

the bike shops have ordered from you. The join that was used is called an equi-join
because the goal is to match the values of a column in one table to the corresponding
values in the second table.</P>
<P>You can further qualify this query by adding more conditions in the <TT>WHERE</TT>
clause. For example:</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
WHERE O.PARTNUM = P.PARTNUM
AND O.PARTNUM = 76</B>

  ORDEREDON NAME           PARTNUM     PARTNUM DESCRIPTION
=========== ========== ===========  ========== ============

 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>
<P>The number <TT>76</TT> is not very descriptive, and you wouldn't want your sales
people to have to memorize a part number. (We have had the misfortune to see many
data information systems in the field that require the end user to know some obscure
code for something that had a perfectly good name. Please don't write one of those!)
Here's another way to write the query:</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
WHERE O.PARTNUM = P.PARTNUM
AND P.DESCRIPTION = 'ROAD BIKE'</B>

  ORDEREDON NAME           PARTNUM     PARTNUM DESCRIPTION
=========== ========== ===========  ========== ============

 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>
<P>Along the same line, take a look at two more tables to see how they can be joined.
In this example the <TT>employee_id</TT> column should obviously be unique. You could
have employees with the same name, they could work in the same department, and earn
the same salary. However, each employee would have his or her own <TT>employee_id</TT>.
To join these two tables, you would use the <TT>employee_id</TT> column.</P>
<P>
<TABLE BORDER="0">
	<TR>
		<TD WIDTH="49%" VALIGN="TOP"><I>EMPLOYEE_TABLE</I></TD>
		<TD WIDTH="144" VALIGN="TOP"><I>EMPLOYEE_PAY_TABLE</I></TD>
	</TR>
	<TR>
		<TD WIDTH="49%" VALIGN="TOP"><TT>employee_id</TT></TD>
		<TD WIDTH="144" VALIGN="TOP"><TT>employee_id</TT></TD>
	</TR>
	<TR>
		<TD WIDTH="49%" VALIGN="TOP"><TT>last_name</TT></TD>
		<TD WIDTH="144" VALIGN="TOP"><TT>salary</TT></TD>
	</TR>
	<TR>
		<TD WIDTH="49%" VALIGN="TOP"><TT>first_name</TT></TD>
		<TD WIDTH="144" VALIGN="TOP"><TT>department</TT></TD>
	</TR>
	<TR>
		<TD WIDTH="49%" VALIGN="TOP"><TT>middle_name</TT></TD>
		<TD WIDTH="144" VALIGN="TOP"><TT>supervisor</TT></TD>
	</TR>
	<TR>
		<TD WIDTH="49%" VALIGN="TOP">&#160;</TD>
		<TD WIDTH="144" VALIGN="TOP"><TT>marital_status</TT></TD>
	</TR>
</TABLE>
</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT E.EMPLOYEE_ID, E.LAST_NAME, EP.SALARY
FROM EMPLOYEE_TBL E,
     EMPLOYEE_PAY_TBL EP
WHERE E.EMPLOYEE_ID = EP.EMPLOYEE_ID
  AND E.LAST_NAME = 'SMITH';</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">E.EMPLOYEE_ID  E.LAST_NAME  EP.SALARY
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">=============  ===========  =========
        13245  SMITH         35000.00
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>When you join two tables without
	the use of a <TT>WHERE</TT> clause, you are performing a Cartesian join. This join
	combines all rows from all the tables in the <TT>FROM</TT> clause. If each table
	has 200 rows, then you will end up with 40,000 rows in your results (200 x 200).
	Always join your tables in the <TT>WHERE</TT> clause unless you have a real need
	to join all the rows of all the selected tables. 
<HR>


</BLOCKQUOTE>

<P>Back to the original tables. Now you are ready to use all this information about
joins to do something really useful: finding out how much money you have made from
selling road bikes:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT SUM(O.QUANTITY * P.PRICE) TOTAL
FROM ORDERS O, PART P
WHERE O.PARTNUM = P.PARTNUM
AND P.DESCRIPTION = 'ROAD BIKE'</B>

      TOTAL
===========

   19610.00
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>With this setup, the sales people can keep the <TT>ORDERS</TT> table updated,
the production department can keep the <TT>PART</TT> table current, and you can find
your bottom line without redesigning your database.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice the consistent use of table
	and column aliases in the SQL statement examples. You will save many, many keystrokes
	by using aliases. They also help to make your statement more readable. 
<HR>


</BLOCKQUOTE>

<P>Can you join more than one table? For example, to generate information to send
out an invoice, you could type this statement:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT C.NAME, C.ADDRESS, (O.QUANTITY * P.PRICE) TOTAL
FROM ORDER O, PART P, CUSTOMER C
WHERE O.PARTNUM = P.PARTNUM
AND O.NAME = C.NAME</B>

NAME       ADDRESS          TOTAL
========== ========== ===========

TRUE WHEEL 55O HUSKER     1200.00
BIKE SPEC  CPT SHRIVE     2400.00
LE SHOPPE  HOMETOWN       3600.00
AAA BIKE   10 OLDTOWN     1200.00
TRUE WHEEL 55O HUSKER     2102.70
BIKE SPEC  CPT SHRIVE     2803.60
TRUE WHEEL 55O HUSKER      196.00
AAA BIKE   10 OLDTOWN      213.50
BIKE SPEC  CPT SHRIVE      542.50
TRUE WHEEL 55O HUSKER     1590.00
BIKE SPEC  CPT SHRIVE     5830.00
JACKS BIKE 24 EGLIN       7420.00
LE SHOPPE  HOMETOWN       2650.00
AAA BIKE   10 OLDTOWN     2120.00
</FONT></PRE>
<P>You could make the output more readable by writing the statement like this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT C.NAME, C.ADDRESS,
O.QUANTITY * P.PRICE TOTAL
FROM ORDERS O, PART P, CUSTOMER C
WHERE O.PARTNUM = P.PARTNUM
AND O.NAME = C.NAME
ORDER BY C.NAME</B>

NAME       ADDRESS          TOTAL
========== ========== ===========

AAA BIKE   10 OLDTOWN      213.50
AAA BIKE   10 OLDTOWN     2120.00
AAA BIKE   10 OLDTOWN     1200.00
BIKE SPEC  CPT SHRIVE      542.50
BIKE SPEC  CPT SHRIVE     2803.60
BIKE SPEC  CPT SHRIVE     5830.00
BIKE SPEC  CPT SHRIVE     2400.00
JACKS BIKE 24 EGLIN       7420.00
LE SHOPPE  HOMETOWN       2650.00
LE SHOPPE  HOMETOWN       3600.00
TRUE WHEEL 55O HUSKER      196.00
TRUE WHEEL 55O HUSKER     2102.70
TRUE WHEEL 55O HUSKER     1590.00
TRUE WHEEL 55O HUSKER     1200.00
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice that when joining the three
	tables (<TT>ORDERS</TT>, <TT>PART</TT>, and <TT>CUSTOMER</TT>) that the <TT>ORDERS</TT>
	table was used in two joins and the other tables were used only once. Tables that
	will return the fewest rows with the given conditions are commonly referred to as
	driving tables, or base tables. Tables other than the base table in a query are usually
	joined to the base table for more efficient data retrieval. Consequently, the <TT>ORDERS</TT>
	table is the base table in this example. In most databases a few base tables join
	(either directly or indirectly) all the other tables. (See Day 15, &quot;Streamlining
	SQL Statements for Improved Performance,&quot; for more on base tables.) 
<HR>


</BLOCKQUOTE>

<P>You can make the previous query more specific, thus more useful, by adding the
<TT>DESCRIPTION</TT> column as in the following example:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT C.NAME, C.ADDRESS,
O.QUANTITY * P.PRICE TOTAL,
P.DESCRIPTION
FROM ORDERS O, PART P, CUSTOMER C
WHERE O.PARTNUM = P.PARTNUM
AND O.NAME = C.NAME
ORDER BY C.NAME</B>

NAME       ADDRESS          TOTAL DESCRIPTION
========== ========== =========== ==============

AAA BIKE   10 OLDTOWN      213.50 TIRES
AAA BIKE   10 OLDTOWN     2120.00 ROAD BIKE
AAA BIKE   10 OLDTOWN     1200.00 TANDEM
BIKE SPEC  CPT SHRIVE      542.50 PEDALS
BIKE SPEC  CPT SHRIVE     2803.60 MOUNTAIN BIKE
BIKE SPEC  CPT SHRIVE     5830.00 ROAD BIKE
BIKE SPEC  CPT SHRIVE     2400.00 TANDEM
JACKS BIKE 24 EGLIN       7420.00 ROAD BIKE
LE SHOPPE  HOMETOWN       2650.00 ROAD BIKE
LE SHOPPE  HOMETOWN       3600.00 TANDEM
TRUE WHEEL 55O HUSKER      196.00 SEATS
TRUE WHEEL 55O HUSKER     2102.70 MOUNTAIN BIKE
TRUE WHEEL 55O HUSKER     1590.00 ROAD BIKE
TRUE WHEEL 55O HUSKER     1200.00 TANDEM
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This information is a result of joining three tables. You can now use this information
to create an invoice.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>In the example at the beginning
	of the day, SQL grouped <TT>TABLE1</TT> and <TT>TABLE2</TT> to create a new table
	with X (rows in <TT>TABLE1</TT>) x Y (rows in <TT>TABLE2</TT>) number of rows. A
	physical table is not created by the join, but rather in a virtual sense. The join
	between the two tables produces a new set that meets all conditions in the <TT>WHERE</TT>
	clause, including the join itself. The <TT>SELECT</TT> statement has reduced the
	number of rows displayed, but to evaluate the <TT>WHERE</TT> clause SQL still creates
	all the possible rows. The sample tables in today's examples have only a handful
	of rows. Your actual data may have thousands of rows. If you are working on a platform
	with lots of horsepower, using a multiple-table join might not visibly affect performance.
	However, if you are working in a slower environment, joins could cause a significant
	slowdown.</P>
	<P>We aren't telling you not to use joins, because you have seen the advantages to
	be gained from a relational design. Just be aware of the platform you are using and
	your customer's requirements for speed versus reliability. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">Non-Equi-Joins</FONT></H2>
<P>Because SQL supports an equi-join, you might assume that SQL also has a non-equi-join.
You would be right! Whereas the equi-join uses an <TT>=</TT> sign in the <TT>WHERE</TT>
statement, the non-equi-join uses everything but an <TT>=</TT> sign. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT O.NAME, O.PARTNUM, P.PARTNUM,
O.QUANTITY * P.PRICE TOTAL
FROM ORDERS O, PART P
WHERE O.PARTNUM &gt; P.PARTNUM</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">NAME           PARTNUM     PARTNUM       TOTAL
========== =========== =========== ===========

TRUE WHEEL          76          54      162.75
BIKE SPEC           76          54      596.75
LE SHOPPE           76          54      271.25
AAA BIKE            76          54      217.00
JACKS BIKE          76          54      759.50
TRUE WHEEL          76          42       73.50
BIKE SPEC           54          42      245.00
BIKE SPEC           76          42      269.50
LE SHOPPE           76          42      122.50
AAA BIKE            76          42       98.00
AAA BIKE            46          42      343.00
JACKS BIKE          76          42      343.00
TRUE WHEEL          76          46       45.75
BIKE SPEC           54          46      152.50
BIKE SPEC           76          46      167.75
LE SHOPPE           76          46       76.25
AAA BIKE            76          46       61.00
JACKS BIKE          76          46      213.50
TRUE WHEEL          76          23     1051.35
TRUE WHEEL          42          23     2803.60
...
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This listing goes on to describe all the rows in the join <TT>WHERE O.PARTNUM
&gt; P.PARTNUM</TT>.<B> </B>In the context of your bicycle shop, this information
doesn't have much meaning, and in the real world the equi-join is far more common
than the non-equi-join. However, you may encounter an application in which a non-equi-join
produces the perfect result.
<H2><FONT COLOR="#000077">Outer Joins versus Inner Joins</FONT></H2>
<P>Just as the non-equi-join balances the equi-join, an outer join complements the
inner join. An inner join is where the rows of the tables are combined with each
other, producing a number of new rows equal to the product of the number of rows
in each table. Also, the inner join uses these rows to determine the result of the
<TT>WHERE</TT> clause. An outer join groups the two tables in a slightly different
way. Using the <TT>PART</TT> and <TT>ORDERS</TT> tables from the previous examples,
perform the following inner join:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE,
O.NAME, O.PARTNUM
FROM PART P
JOIN ORDERS O ON ORDERS.PARTNUM = 54</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">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