📄 ch06.htm
字号:
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"> </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, "Streamlining
SQL Statements for Improved Performance," 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 > 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
> 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
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -