UNION Operator and the Outer Join - Teradata Vantage

Example: Performing a Union Operation to Find Hours Worked does not show 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. Nonmatching rows are extended with NULLs.

However, you can 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 preceding UNION operation returns results equivalent to the results of the preceding left outer join example.