![]() |
||||||
![]()
|
![]() |
|
![]() |
Teach Yourself SQL in 21 Days, Second Edition- Day 6 -
|
EMPLOYEE_TABLE | EMPLOYEE_PAY_TABLE |
employee_id | employee_id |
last_name | salary |
first_name | department |
middle_name | supervisor |
marital_status |
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';
E.EMPLOYEE_ID E.LAST_NAME EP.SALARY
============= =========== ========= 13245 SMITH 35000.00
TIP: When you join two tables without the use of a WHERE clause, you are performing a Cartesian join. This join combines all rows from all the tables in the FROM 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 WHERE clause unless you have a real need to join all the rows of all the selected tables.
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:
SELECT SUM(O.QUANTITY * P.PRICE) TOTAL FROM ORDERS O, PART P WHERE O.PARTNUM = P.PARTNUM AND P.DESCRIPTION = 'ROAD BIKE' TOTAL =========== 19610.00
With this setup, the sales people can keep the ORDERS table updated, the production department can keep the PART table current, and you can find your bottom line without redesigning your database.
NOTE: 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.
Can you join more than one table? For example, to generate information to send out an invoice, you could type this statement:
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 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
You could make the output more readable by writing the statement like this:
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 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
NOTE: Notice that when joining the three tables (ORDERS, PART, and CUSTOMER) that the ORDERS 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 ORDERS 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.)
You can make the previous query more specific, thus more useful, by adding the DESCRIPTION column as in the following example:
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 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
This information is a result of joining three tables. You can now use this information to create an invoice.
NOTE: In the example at the beginning of the day, SQL grouped TABLE1 and TABLE2 to create a new table with X (rows in TABLE1) x Y (rows in TABLE2) 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 WHERE clause, including the join itself. The SELECT statement has reduced the number of rows displayed, but to evaluate the WHERE 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.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.
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 = sign in the WHERE statement, the non-equi-join uses everything but an = sign. For example:
SELECT O.NAME, O.PARTNUM, P.PARTNUM, O.QUANTITY * P.PRICE TOTAL FROM ORDERS O, PART P WHERE O.PARTNUM > P.PARTNUM
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 ...
This listing goes on to describe all the rows in the join WHERE O.PARTNUM > P.PARTNUM. 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.
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 WHERE clause. An outer join groups the two tables in a slightly different way. Using the PART and ORDERS tables from the previous examples, perform the following inner join:
SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE, O.NAME, O.PARTNUM FROM PART P JOIN ORDERS O ON ORDERS.PARTNUM = 54
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
NOTE: The syntax you used to get this join--JOIN ON--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 WHERE clause in this type of join.
The result is that all the rows in PART are spliced on to specific rows in ORDERS where the column PARTNUM is 54. Here's a RIGHT OUTER JOIN statement:
SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE, O.NAME, O.PARTNUM FROM PART P RIGHT OUTER JOIN ORDERS O ON ORDERS.PARTNUM = 54 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
This type of query is new. First you specified a RIGHT OUTER JOIN, which caused SQL to return a full set of the right table, ORDERS, and to place nulls in the fields where ORDERS.PARTNUM <> 54. Following is a LEFT OUTER JOIN statement:
SELECT P.PARTNUM, P.DESCRIPTION,P.PRICE, O.NAME, O.PARTNUM FROM PART P LEFT OUTER JOIN ORDERS O ON ORDERS.PARTNUM = 54 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
You get the same six rows as the INNER JOIN. Because you specified LEFT (the LEFT table), PART determined the number of rows you would return. Because PART is smaller than ORDERS, SQL saw no need to pad those other fields with blanks.
Don't worry too much about inner and outer joins. Most SQL products determine the optimum JOIN 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.
Some implementations of SQL use the + sign instead of an OUTER JOIN statement. The + simply means "Show me everything even if something is missing." Here's the syntax:
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';
This statement is joining the two tables. The + sign on the ep.employee_id column will return all rows even if they are empty.
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 TABLE1 to itself, type this:
SELECT * FROM TABLE1, TABLE1
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 ...
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 PARTNUM 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 PARTNUM in the following table:
SELECT * FROM PART 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 #
You saved your company from this bad situation by checking PART before anyone used it:
SELECT F.PARTNUM, F.DESCRIPTION, S.PARTNUM,S.DESCRIPTION FROM PART F, PART S WHERE F.PARTNUM = S.PARTNUM AND F.DESCRIPTION <> S.DESCRIPTION PARTNUM DESCRIPTION PARTNUM DESCRIPTION ========== ======================== ======= ============ 76 ROAD BIKE 76 CLIPPLESS SHOE 76 CLIPPLESS SHOE 76 ROAD BIKE
Now you are a hero until someone asks why the table has only two entries. You, remembering what you have learned about JOINs, retain your hero status by explaining how the join produced two rows that satisfied the condition WHERE F.PARTNUM = S.PARTNUM AND F.DESCRIPTION <> S.DESCRIPTION. Of course, at some point, the row of data containing the duplicate PARTNUM would have to be corrected.
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.
Congratulations--you have learned almost everything there is to know about the SELECT clause. The one remaining item, subqueries, is covered tomorrow (Day 7, "Subqueries: The Embedded SELECT Statement").
A 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.
Q How many tables can you join on?
A 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.
Q Would it be fair to say that when tables are joined, they actually become one table?
A 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.
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."
2. What type of join appears in the following SELECT statement?
select e.name, e.employee_id, ep.salary from employee_tbl e, employee_pay_tbl ep where e.employee_id = ep.employee_id;
a. select name, employee_id, salary
from employee_tbl e,
employee_pay_tbl ep
where employee_id = employee_id
and name like '%MITH';
b. select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where name like '%MITH';
c. select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where e.employee_id = ep.employee_id
and e.name like '%MITH';
5. In joining tables are you limited to one-column joins, or can you join on more than one column?
2. Rewrite the following query to make it more readable and shorter.
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;
ORDEREDON NAME PARTNUM QUANTITY ================== ================== ======= ======== 2-SEP-96 TRUE WHEEL 10 1
© Copyright, Macmillan Computer Publishing. All rights reserved.
![]() |
|
Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details. |