the bike shops have ordered from you. The join that was used is called an equi-joinbecause the goal is to match the values of a column in one table to the correspondingvalues 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.DESCRIPTIONFROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUMAND O.PARTNUM = 76</B>  ORDEREDON NAME           PARTNUM     PARTNUM DESCRIPTION=========== ========== ===========  ========== ============ 1-JUL-1996 AAA BIKE            76          76 ROAD BIKE17-JAN-1996 BIKE SPEC           76          76 ROAD BIKE19-MAY-1996 TRUE WHEEL          76          76 ROAD BIKE11-JUL-1996 JACKS BIKE          76          76 ROAD BIKE17-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 salespeople to have to memorize a part number. (We have had the misfortune to see manydata information systems in the field that require the end user to know some obscurecode 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.DESCRIPTIONFROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUMAND P.DESCRIPTION = 'ROAD BIKE'</B>  ORDEREDON NAME           PARTNUM     PARTNUM DESCRIPTION=========== ========== ===========  ========== ============ 1-JUL-1996 AAA BIKE            76          76 ROAD BIKE17-JAN-1996 BIKE SPEC           76          76 ROAD BIKE19-MAY-1996 TRUE WHEEL          76          76 ROAD BIKE11-JUL-1996 JACKS BIKE          76          76 ROAD BIKE17-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 couldhave employees with the same name, they could work in the same department, and earnthe 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.SALARYFROM EMPLOYEE_TBL E,     EMPLOYEE_PAY_TBL EPWHERE 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 aboutjoins to do something really useful: finding out how much money you have made fromselling road bikes:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF"><B>SELECT SUM(O.QUANTITY * P.PRICE) TOTALFROM ORDERS O, PART PWHERE O.PARTNUM = P.PARTNUMAND 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 findyour 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 sendout 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) TOTALFROM ORDER O, PART P, CUSTOMER CWHERE O.PARTNUM = P.PARTNUMAND O.NAME = C.NAME</B>NAME       ADDRESS          TOTAL========== ========== ===========TRUE WHEEL 55O HUSKER     1200.00BIKE SPEC  CPT SHRIVE     2400.00LE SHOPPE  HOMETOWN       3600.00AAA BIKE   10 OLDTOWN     1200.00TRUE WHEEL 55O HUSKER     2102.70BIKE SPEC  CPT SHRIVE     2803.60TRUE WHEEL 55O HUSKER      196.00AAA BIKE   10 OLDTOWN      213.50BIKE SPEC  CPT SHRIVE      542.50TRUE WHEEL 55O HUSKER     1590.00BIKE SPEC  CPT SHRIVE     5830.00JACKS BIKE 24 EGLIN       7420.00LE SHOPPE  HOMETOWN       2650.00AAA 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 TOTALFROM ORDERS O, PART P, CUSTOMER CWHERE O.PARTNUM = P.PARTNUMAND O.NAME = C.NAMEORDER BY C.NAME</B>NAME       ADDRESS          TOTAL========== ========== ===========AAA BIKE   10 OLDTOWN      213.50AAA BIKE   10 OLDTOWN     2120.00AAA BIKE   10 OLDTOWN     1200.00BIKE SPEC  CPT SHRIVE      542.50BIKE SPEC  CPT SHRIVE     2803.60BIKE SPEC  CPT SHRIVE     5830.00BIKE SPEC  CPT SHRIVE     2400.00JACKS BIKE 24 EGLIN       7420.00LE SHOPPE  HOMETOWN       2650.00LE SHOPPE  HOMETOWN       3600.00TRUE WHEEL 55O HUSKER      196.00TRUE WHEEL 55O HUSKER     2102.70TRUE WHEEL 55O HUSKER     1590.00TRUE 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.DESCRIPTIONFROM ORDERS O, PART P, CUSTOMER CWHERE O.PARTNUM = P.PARTNUMAND O.NAME = C.NAMEORDER BY C.NAME</B>NAME       ADDRESS          TOTAL DESCRIPTION========== ========== =========== ==============AAA BIKE   10 OLDTOWN      213.50 TIRESAAA BIKE   10 OLDTOWN     2120.00 ROAD BIKEAAA BIKE   10 OLDTOWN     1200.00 TANDEMBIKE SPEC  CPT SHRIVE      542.50 PEDALSBIKE SPEC  CPT SHRIVE     2803.60 MOUNTAIN BIKEBIKE SPEC  CPT SHRIVE     5830.00 ROAD BIKEBIKE SPEC  CPT SHRIVE     2400.00 TANDEMJACKS BIKE 24 EGLIN       7420.00 ROAD BIKELE SHOPPE  HOMETOWN       2650.00 ROAD BIKELE SHOPPE  HOMETOWN       3600.00 TANDEMTRUE WHEEL 55O HUSKER      196.00 SEATSTRUE WHEEL 55O HUSKER     2102.70 MOUNTAIN BIKETRUE WHEEL 55O HUSKER     1590.00 ROAD BIKETRUE 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 informationto 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 TOTALFROM ORDERS O, PART PWHERE 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.75BIKE SPEC           76          54      596.75LE SHOPPE           76          54      271.25AAA BIKE            76          54      217.00JACKS BIKE          76          54      759.50TRUE WHEEL          76          42       73.50BIKE SPEC           54          42      245.00BIKE SPEC           76          42      269.50LE SHOPPE           76          42      122.50AAA BIKE            76          42       98.00AAA BIKE            46          42      343.00JACKS BIKE          76          42      343.00TRUE WHEEL          76          46       45.75BIKE SPEC           54          46      152.50BIKE SPEC           76          46      167.75LE SHOPPE           76          46       76.25AAA BIKE            76          46       61.00JACKS BIKE          76          46      213.50TRUE WHEEL          76          23     1051.35TRUE 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 informationdoesn't have much meaning, and in the real world the equi-join is far more commonthan the non-equi-join. However, you may encounter an application in which a non-equi-joinproduces 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 theinner join. An inner join is where the rows of the tables are combined with eachother, producing a number of new rows equal to the product of the number of rowsin 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 differentway. 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.PARTNUMFROM PART PJOIN 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