Right Outer Join | SQL Joins | Teradata Vantage - Right Outer Join - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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_ATable_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;
o.course_no o.location e.emp_no
C100 El Segundo 236
C100 El Segundo 668
Null Null 236

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