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

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 to indicate how these terms relate to the way a simple right 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 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;

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.

For more informative results, use 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, Null, 236), not (Null, Null, 236).