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 preceding UNION operation returns results equivalent to the results of the previous left outer join example.
O.CourseNo | O.Location | E.EmpNo |
---|---|---|
C100 | El Segundo | 235 |
C100 | El Segundo | 668 |
C200 | Dayton | ? |
C400 | El Segundo | ? |