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

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

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 ?