Left Outer Join
Definition
When you perform a left outer join on the Offerings and Enrollment tables, the rows from the left table that are not returned in the result of the inner join of these two tables are returned in the outer join result and extended with nulls.
Inner/Outer Table Example
The following example uses the explicit table names inner_table and outer_table (see “Terminology” on page 258) to indicate how these terms relate to the way a simple left outer join is constructed in the FROM clause of a SELECT statement.
The example shows the semantics of inner and outer table references for a left outer join.
outer_table LEFT OUTER JOIN inner_table
Section 1 represents the inner join (intersection) of outer_table and inner_table. Section 2 represents the unmatched rows from the outer table.
The outer join result contains the matching rows from Sections 2 and 3, indicated in the diagram as Section 1, plus unmatched rows from Section 2, noted in the graphic by the more lightly shaded component of the Venn diagram.
In terms of the algebra of sets, the result is
(Table_A ∩ Table_B) + (Table_A - Table_B)
where:
Table_A ∩ Table_B is the set of matched rows from the inner join of Table_A and Table_B.
Table_A - Table_B is the set of unmatched rows from Table_A.
Practical Example of a Left Outer Join
The following SELECT statement yields the results in the table that follows:
SELECT offerings.course_no,offerings.location,enrollment.emp_no
FROM offerings
LEFT OUTER JOIN enrollment ON offerings.course_no =
enrollment.courseno;
o.course_no
-----------
|
o.location
----------
|
e.emp_no
--------
|
C100
|
El Segundo
|
236
|
C100
|
El Segundo
|
668
|
C200
|
Dayton
|
?
|
C400
|
El Segundo
|
?
|
Note that BTEQ reports represent nulls with the QUESTION MARK character.
These results show that course C100 has two enrolled employees and that courses C200 and C400 have no enrolled employees. In this case, the nulls returned by the outer join of the offerings and enrollment tables provide meaningful information.
The keyword OUTER in the FROM clause is optional, so you can also write the above SELECT statement as follows:
SELECT offerings.course_no,offerings.location,enrollment.emp_no
FROM offerings
LEFT JOIN enrollment ON offerings.course_no = employee.course_no;