Full Outer Join - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.00
Language
English (United States)
Last Update
2018-09-28
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Full Outer Join

Definition

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.

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 full 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 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.

Practical Example of a 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, as well as the classes requested (but not offered) and the employees who requested them.

The example uses the same two tables as the prior example, offerings and enrollment.

 

Performing a full outer join on these two tables results in 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;
 

Note that 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.

In this case, the nulls returned by the outer join of the offerings and enrollment tables are meaningful information.

You can see that course C300, for which employee 236 registered, is not among those courses offered by customer education, nor does it have a location. Notice the null in the last row of the o.course_no and the o.location columns.