Right 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

Right Outer Join

Definition

In a right outer join, the rows from the right table that are 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 right 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 right outer join.

     inner_table RIGHT OUTER JOIN outer_table

Section 1 represents the inner join (intersection) of outer_table and inner_table. Section 3 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 the unmatched rows from Section 3, noted in the graphic by the more darkly shaded component of the Venn diagram.

In terms of the algebra of sets, the result is

     (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_B - Table_A is the set of unmatched rows from Table_B.

Practical Example of a Right Outer Join

When you perform a right outer join on the offerings and enrollment tables, the rows from the right table that are not returned in the result of the inner join are returned in the outer join result and extended with nulls.

This SELECT statement returns the results in the following table:

     SELECT offerings.course_no, offerings.location, enrollment.emp_no
     FROM offerings 
     RIGHT 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 in it and that employee 236 has not enrolled in another class. But in this case the nulls returned by the right outer join of the offerings and enrollment tables are deceptive, because we know by inspection of the enrollment table that employee 236 has enrolled for course C300. We also know by inspection of the offerings table that course C300 is not currently being offered.

To get the more useful results you probably want, write the following right outer join:

     SELECT enrollment.course_no,offerings.location,enrollment.emp_no
     FROM offerings
     RIGHT OUTER JOIN enrollment 
     ON offerings.course_no = enrollment.course_no;

This query returns the row (C300, ?, 236), not (?, ?, 236).