Missing In Action: The Full Outer Join
April 11, 2007 Hey, Ted
|
As far as I know, you haven’t covered my situation in Four Hundred Guru. I need to join two physical files using SQL. It’s possible that some records in the first file won’t have matches in the second file. It’s also possible that some records in the second file won’t have matches in the first file. What type of join do I use? –Chris You need a full outer join, Chris, and DB2/400 (or whatever they call it these days) won’t handle it–yet. I expect IBM to add the full outer join any release now. To simulate the full outer join, I use the following formula: Left outer join + right exception join = full outer join. Suppose you work for an institution of higher learning of some sort, and need a list of the professors and the classes each one has been assigned to teach. Some professors have not yet been assigned to teach any classes. Some classes have not yet been assigned to a professor. How would you get a full schedule? First, here is the FACULTY table:
And here is the SCHEDULE table, which lists all the classes.
Notice a few things:
Here’s the join:
SELECT f.FacID, f.Name, s.classID, s.period, s.Building, s.Room
FROM Faculty AS f
LEFT JOIN Schedule AS s
ON f.FacID = s.Instructor
UNION
SELECT s.Instructor, f.Name, s.classID, s.period, s.Building, s.Room
FROM Faculty AS f
RIGHT EXCEPTION JOIN Schedule AS s
ON f.FacID = s.Instructor
Notice that the two SELECT expressions are almost identical. They differ in the type of join, of course. In the second SELECT, I placed the instructor ID from the secondary table, rather than from the primary table, because any values from the primary table will always be null in a right exception join. Here is the result set.
That technique will have to do us until the database team in Rochester adds a full outer join to the SQL syntax. I am surprised that I have not covered this technique in the newsletter already. Nobody ever asked. –Ted
|
