Page 204
A simple two-table join can illustrate the use of this syntax. The Class table identifies the instructor for a class by Instructor_ID. The Instructor table contains detailed information about each instructor. By joining the Class table with the Instructor table based on the Instructor_ID column, you can retrieve additional customer data.
Listing 8.17. A simple two-table join.
SQL> select Class_ID, Course_ID, Last_Name, First_Name, Position
2 from Class C, Instructor I
3 where
4 C.Instructor_ID = I.Instructor_ID
5 order by Class_ID;
CLASS_ID COURS LAST_NAME FIRST_NAME POSITION
---------- ----- --------------- --------------- ----------------------
103400 183 JASON JERROLD ASSOCIATE PROFESSOR
103600 50 CHANG ROGER ASSISTANT PROFESSOR
104200 198 RICHARDSON NANCY ASSISTANT PROFESSOR
104500 184 RESTON RAPHAEL ASSOCIATE PROFESSOR
108300 101 CHERNOW BESS ASSOCIATE PROFESSOR
108400 115 TORRES PETER PROFESSOR
108600 183 JASON JERROLD ASSOCIATE PROFESSOR
109100 101 WEISS ROBERTA PROFESSOR
110300 199 CHU STEVEN PROFESSOR
120200 199 PARKER WILLIAM PROFESSOR
10 rows selected.
Look at each element of the SELECT statement. The select list consists of five columns: Class_ID, Course_ID, Last_Name, First_Name, and Position. The tables in the FROM clause are Class and Instructor. The WHERE clause instructs the Oracle RDBMS to return only rows in which Instructor_ID in the Class table can be matched with a row in the Instructor table that has the same value for Instructor_ID.
There is no restriction on the order of the columns in the select-list. Also, there is no requirement that one or more columns even be selected from each table in the join. For example, Listing 8.18 shows a valid join in which the select list contains no columns from the Class table.
Listing 8.18. Join select list containing columns from only one table.
SQL> select Last_Name, First_Name, Position 2 from Class C, Instructor I 3 where
Page 205
4 C.Instructor_ID = I.Instructor_ID
5 order by Class_ID;
LAST_NAME FIRST_NAME POSITION
-------------- -------------- ------------------------
JASON JERROLD ASSOCIATE PROFESSOR
CHANG ROGER ASSISTANT PROFESSOR
RICHARDSON NANCY ASSISTANT PROFESSOR
RESTON RAPHAEL ASSOCIATE PROFESSOR
CHERNOW BESS ASSOCIATE PROFESSOR
TORRES PETER PROFESSOR
JASON JERROLD ASSOCIATE PROFESSOR
WEISS ROBERTA PROFESSOR
CHU STEVEN PROFESSOR
PARKER WILLIAM PROFESSOR
10 rows selected.
Each reference to a column in a join must be unambiguous. In this context, unambiguous means that if the column exists in more than one of the tables referenced in the join, the column name is qualified by the table name. Oracle returns an error message if you reference a column ambiguously. Listing 8.19 demonstrates a join that contains an ambiguous column—Instructor_ID.
Listing 8.19. Ambiguous column in a join.
SQL> select Instructor_ID, Position
2 from Class C, Instructor I
3 where
4 C.Instructor_ID = I.Instructor_ID
5 order by Class_ID;
select Instructor_ID, Position
*
ERROR at line 1:
ORA-00918: column ambiguously defined
Oracle returns the error message because Instructor_ID, which is part of the select
list, appears in both the Class and Instructor tables. To correct this problem, you must
qualify the Instructor_ID column with the table name or alias. Listing 8.20 demonstrates how
an ambiguous column can be qualified in the select list.
Page 206
Listing 8.20. Qualifying an ambiguous column in the select list.
SQL> select I.Instructor_ID, Position
2 from Class C, Instructor I
3 where
4 C.Instructor_ID = I.Instructor_ID
5 order by Class_ID;
INSTRUCTOR_ID POSITION
-------------------- ------------------------
J505 ASSOCIATE PROFESSOR
E405 ASSISTANT PROFESSOR
E491 ASSISTANT PROFESSOR
D944 ASSOCIATE PROFESSOR
T149 ASSOCIATE PROFESSOR
G331 PROFESSOR
J505 ASSOCIATE PROFESSOR
W490 PROFESSOR
S131 PROFESSOR
R983 PROFESSOR
10 rows selected.
When you're first learning to join multiple tables, a common error is to forget to provide a join condition in the WHERE clause. If you forget a join condition, you notice two things: the query takes considerably longer to execute, and the number of retrieved records is much larger than you expect.
NEW TERM
The technical term for a join that does not have a join condition is a Cartesian product.
Consider the example shown in Listing 8.21. Two tables, Class and Instructor, are listed in the FROM clause without a join condition. The Class table has 10 rows and the Class Location table has 13 rows. The query does not have a join condition instructing Oracle to retrieve the columns from each table based on Class_Building and Class_Room. As a result, Oracle returns all possible combinations of the two tables, which is 10 times 13, or 130 rows.
Listing 8.21. Cartesian product produced by a join that is missing a join condition.
SQL> select Class_ID, Seating_Capacity
2 from Class, Class_Location
3 order by Class_ID;
Page 207
CLASS_ID SEATING_CAPACITY ---------- ---------------- 103400 200 103400 120 103400 80 103400 45 103400 35 103400 45 103400 45 103400 90 103400 50 ... 108600 200 108600 80 108600 90 108600 50 109100 45 109100 40 110300 50 110300 45 110300 40 110300 45 110300 40 110300 35 120200 80 130 rows selected.
As you see, the syntax for the SELECT statement doesn't limit the number of tables that can be joined. As an example, refer to Listing 8.22, which joins three tables—Class, Course, and Instructor—to retrieve detailed information about each class.
Listing 8.22. A three-table join.
SQL> select Class_ID, CL.Course_ID, CL.Department_ID, Title, Last_Name
2 from Class CL, Course CO, Instructor I
3 where
4 CL.Course_ID = CO.Course_ID and
5 CL.Department_ID = CO.Department_ID and
6 CL.Instructor_ID = I.Instructor_ID
7 order by Class_ID;
CLASS_ID COURS DEPARTME TITLE LAST_NAME
---------- ----- -------- ------------------------------ --------------
103400 183 PSYCH WORKSHOP ON NORMALITY JASON
103600 50 MATH PRE-CALCULUS CHANG
104200 198 PHILO MODERN PHILOSOPHY RICHARDSON
104500 184 HIST MODERN EUROPEAN HISTORY RESTON
108300 101 ENGL INTRO TO ENGLISH LIT CHERNOW
108400 115 HIST EARLY AMERICAN HISTORY TORRES
continues