Page 208
Listing 8.22. continued
108600 183 PSYCH WORKSHOP ON NORMALITY JASON 109100 101 BIO INTRO TO BIOLOGY WEISS 110300 199 ENG SEMINAR ON CHAOS CHU 120200 199 ECON WORKSHOP ON MARX PARKER 10 rows selected.
Notice that an alias has been specified for each of the three tables:
- CL for Class
- CO for Course
- I for Instructor
Although table aliases are optional, you should use them in multiple-table joins because they reduce the size of the SELECT statement and simplify its appearance.
Examine each of the join conditions found in Listing 8.22. The first two join conditions are used to join the Class and Course tables based on Course_ID and Department_ID. These tables have two join conditions because the primary key of the Course table is Course_ID and Department_ID. The third join condition is used to join the Class and Instructor tables based on Instructor_ID.
Self-Join
Another form of a two-table join is the self-join. This type of join is used when a table has a foreign key that references its own primary key. A good example of this type is the Product_Assembly table discussed earlier in this lesson. The primary key of the Product Assembly table is Product_ID. Every product—except a complete product—belongs to an assembly. Therefore, Assembly_ID is a foreign key that references Product_ID (see Figure 8.1).
Figure 8.1.Example of a table's
primary key referenced
by its foreign key.
)
Page 209
As an example of a self-join, suppose that you want to retrieve the description of each product and the description of the assembly to which it belongs. As Listing 8.23 illustrates, the join statement must define an alias for both copies of the table. The join condition—P1.Assembly_ID = P2.Product_ID—causes Oracle to retrieve product information from P1 and assembly information from P2.
Listing 8.23. Example of a self-join.
SQL> select P1.Description || ` is part of ` || P2.Description
"Assembly Breakdown"
2 from Product_Assembly P1, Product_Assembly P2
3 where
4 P1.Assembly_ID = P2.Product_ID
5 order by P1.Assembly_ID, P1.Product_ID;
Assembly Breakdown
----------------------------------------------------------------------
Titanium alloy, teflon-coated iris is part of Black body, stainless ste
Variable-speed shutter, standard is part of Black body, stainless steel
Manual film advance, winder is part of Black body, stainless steel fram
Black body, stainless steel frame camera body is part of Complete camer
Blue filter - standard is part of Complete camera
Xenon flash unit is part of Complete camera
100mm lens - standard is part of Complete camera
4 foot camera strap, leather is part of Complete camera
8 rows selected.
Outer Joins
In this section, you'll see how another type of join is used—the outer join. To understand the difference between a regular join (that is, an equi-join) and an outer join, let's take another look at a regular join. A multiple-table join returns columns from each table where the join conditions are met. Consider the example shown in Listing 8.24. The instructors at Flugle College may or may not be scheduled to teach a class during a particular semester. In addition to teaching, the staff members at Flugle College are also expected to be engaged in original research in their fields. Listing 8.24 contains a join of the Instructor and Class tables. Notice that the query only retrieves instructors who are scheduled to teach a class.
Listing 8.24. Equi-join of the Instructor and Class tables.
SQL> select I.Department_ID, Last_Name, First_Name, Class_ID
2 from Instructor I, Class C
3 where
4 I.Instructor_ID = C.Instructor_ID
5 order by I.Department_ID, Last_Name;
continues
Page 210
Listing 8.24. continued
DEPARTME LAST_NAME FIRST_NAME CLASS_ID -------- --------------- --------------- ---------- BIO WEISS ROBERTA 109100 ECON PARKER WILLIAM 120200 ENG CHU STEVEN 110300 ENGL CHERNOW BESS 108300 HIST RESTON RAPHAEL 104500 HIST TORRES PETER 108400 MATH CHANG ROGER 103600 PHILO RICHARDSON NANCY 104200 PSYCH JASON JERROLD 108600 PSYCH JASON JERROLD 103400 10 rows selected.
If you want to retrieve instructor information whether or not an instructor is teaching a class, you must use the outer join operator by appending (+) (a plus sign within parentheses) to the optional column in the join condition. Listing 8.25 illustrates the use of the outer join operator.
Listing 8.25. An outer join.
SQL> select I.Department_ID, Last_Name, First_Name, Class_ID
2 from Instructor I, Class C
3 where
4 I.Instructor_ID = C.Instructor_ID (+)
5 order by I.Department_ID, Last_Name;
DEPARTMENT_ID LAST_NAME FIRST_NAME CLASS_ID
-------------------- --------------- ---------- --------------------
ANTHRO DANIELS LAURA
ANTHRO HITCHCOCK BORIS
ANTHRO POULSON RANIER
BIO EDWARDS SAMANTHA
BIO WEISS ROBERTA 109100
ECON PARKER WILLIAM 120200
ENG CHU STEVEN 110300
ENGL CHERNOW BESS 108300
HIST RESTON RAPHAEL 104500
HIST TORRES PETER 108400
MATH CHANG ROGER 103600
MATH NILAND MARTINA
MATH YOUNG ALLAN
PHILO ANGELO ARTHUR
PHILO BILLINGS BENJAMIN
PHILO RICHARDSON NANCY 104200
PSYCH BATES JOSEPH
PSYCH JASON JERROLD 108600
PSYCH JASON JERROLD 103400
19 rows selected.
Page 211
If an instructor is not teaching a class, Oracle returns a null for the Class_ID—or any other column that you select from the Class table. Listing 8.26 demonstrates how you can obtain a list of instructors who are not teaching a class by adding another condition to the WHERE clause—Class_ID is null.
Listing 8.26. Another outer join.
SQL> select I.Department_ID, Last_Name, First_Name
2 from Instructor I, Class C
3 where
4 I.Instructor_ID = C.Instructor_ID (+) and
5 Class_ID is null
6 order by I.Department_ID, Last_Name;
DEPARTMENT_ID LAST_NAME FIRST_NAME
-------------------- --------------- ----------
ANTHRO DANIELS LAURA
ANTHRO HITCHCOCK BORIS
ANTHRO POULSON RANIER
BIO EDWARDS SAMANTHA
MATH NILAND MARTINA
MATH YOUNG ALLAN
PHILO ANGELO ARTHUR
PHILO BILLINGS BENJAMIN
PSYCH BATES JOSEPH
9 rows selected.
Using Set Operators in the SELECT Statement
The SQL language is a partial implementation of the relational model as envisioned by Codd, the father of relational theory. As part of that implementation, Oracle's version of SQL provides three set operators: INTERSECT, UNION, and MINUS.
The INTERSECT Operator
The INTERSECT operator returns the rows that are common between two sets of rows.
The syntax for using the INTERSECT operator is
select-stmt1 INTERSECT select-stmt2 [order-by-clause]