Missing In Action: The Full Outer Join

Published: 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:

• No classes have been assigned to instructor P01.
• Class 104 has not been assigned to an instructor.
• Class 105 has been assigned to non-existent instructor P04.

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

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot