Full Outer Join | SQL Joins | Teradata Vantage - Full Outer Join - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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

offerings   enrollment
course_no beginning_dates location   emp_no course_no
C100 01/05/94 El Segundo   236 C100
C200 07/02/94 Dayton   236 C300
C400 10/07/94 El Segundo   668 C100

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;
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

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.