UNION Operator and the Outer Join - Teradata Database - Teradata Vantage NewSQL Engine

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.