Left Outer Join | SQL Joins | Teradata Vantage - Left 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-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

When you perform a left outer join on the Offerings and Enrollment tables, the rows from the left table that are not returned in the result of the inner join of these two tables 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 left 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 left outer join.

     outer_table  LEFT OUTER JOIN  inner_table


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

In terms of the algebra of sets, the result is

     (Table_A ∩ Table_B) + (Table_A - Table_B)

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.

Practical Example of a Left Outer Join

The following SELECT statement yields the results in the table that follows:

     SELECT offerings.course_no,offerings.location,enrollment.emp_no
     FROM offerings
     LEFT OUTER JOIN enrollment ON offerings.course_no =
                     enrollment.courseno;
o.course_no o.location e.emp_no
C100 El Segundo 236
C100 El Segundo 668
C200 Dayton Null
C400 El Segundo Null

BTEQ reports represent nulls with the QUESTION MARK character.

These results show that course C100 has two enrolled employees and that courses C200 and C400 have no enrolled employees. In this case, the nulls returned by the outer join of the offerings and enrollment tables provide meaningful information.

The keyword OUTER in the FROM clause is optional, so you can also write the preceding SELECT statement as follows:

     SELECT offerings.course_no,offerings.location,enrollment.emp_no
     FROM offerings
     LEFT JOIN enrollment ON offerings.course_no = employee.course_no;