In a full outer join, rows from both tables that have not been returned in the result of the inner join are returned in the outer join result and extended with nulls.
Example: Inner/Outer Table
The following example uses the explicit table names inner table and outer table to indicate how these terms relate to the way a simple full outer join is constructed in the FROM clause of a SELECT statement. See Inner Table and Outer Table.
The example shows the semantics of inner and outer table references for a full outer join.
outer_table_1 FULL OUTER JOIN outer_table_2
Section 1 represents the inner join (intersection) of outer_table_1 and outer_table_2. Sections 2 and 3 represent the unmatched rows from both outer tables.
The outer join result contains matched rows from the inner join of table_a with table_b plus the unmatched rows from table_a and table_b.
In terms of the algebra of sets, the result is
(Table_A ∩ Table_B) + (Table_A - Table_B) + (Table_B - Table_A)
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.
(Table_B - Table_A) is the set of unmatched rows from table_b.
Example: Full Outer Join
Suppose you want to find out if the current course offering by customer education satisfied the employee requests for courses, and include the courses offered and their locations and the classes requested (but not offered) and the employees who requested them.
The example uses the tables offerings and enrollment from Definition of the Outer Join.
Performing a full outer join on these two tables causes the rows from both tables not returned in the result of the inner join being returned in the outer join and extended with nulls.
The following SELECT statement yields the desired results, as indicated in the results table that follows:
SELECT offerings.course_no, offerings.location, enrollment.course_no,enrollment.emp_no, FROM offerings FULL OUTER JOIN enrollment ON offerings.course_no = enrollment.course_no;
| o.course_no | o.location | e.course_no | e.emp_no |
|---|---|---|---|
| C100 | El Segundo | C100 | 236 |
| C100 | El Segundo | C100 | 668 |
| C200 | Dayton | Null | Null |
| C400 | El Segundo | Null | Null |
| Null | Null | C300 | 236 |
BTEQ reports represent nulls with the QUESTION MARK character.
These results show that course C100 has two employees enrolled and that courses C200 and C400 have no employees enrolled.
The nulls returned by the outer join of the offerings and enrollment tables are meaningful information.
Course C300, for which employee 236 registered, is not among those courses offered by customer education and does not have a location, thus the null in the last row of the o.course_no and the o.location columns.