17.05 - UNION Operator and the Outer Join - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

Example: Performing a Union Operation to Find Hours Worked does not illustrate an outer join. That operation returns all rows in the joined tables for which there is a match on the join condition and rows from the “left” join table, or the “right” join table, or both tables for which there is no match. Moreover, non-matching rows are extended with NULLs.

It is possible, however, to achieve an outer join using inner joins and the UNION operator, though the union of any two inner joins is not the equivalent of an outer join.

The following example shows how to achieve an outer join using two inner joins and the UNION operator. Notice how the second inner join uses NULLs.

   SELECT Offering.CourseNo, Offerings.Location, Enrollment.EmpNo
   FROM Offerings, Enrollment
   WHERE Offerings.CourseNo = Enrollment.CourseNo
   UNION
   SELECT Offerings.CourseNo, Offerings.Location, NULL
   FROM Offerings, Enrollment
   WHERE Offerings.CourseNo <> Enrollment.CourseNo;

The above UNION operation returns results equivalent to the results of the left outer join example shown above.

O.CourseNo O.Location E.EmpNo
C100 El Segundo 235
C100 El Segundo 668
C200 Dayton ?
C400 El Segundo ?