Simulating a full outer join

 

Like the left and right outer joins, a full outer join returns matching rows from both tables. However, a full outer join also returns nonmatching rows from both tables.

While DB2^® UDB for iSeries™ does not support the full outer join syntax, you can simulate a full outer join by using a left outer join and a right exception join. Suppose that you want to find all employees and all of their projects. You want to see those employees that are not currently in charge of a project as well. The following query returns a list of all employees whose names are greater than 'S', along with their assigned project numbers:

SELECT EMPNO, LASTNAME, PROJNO      FROM CORPDATA.EMPLOYEE LEFT OUTER JOIN CORPDATA.PROJECT            ON EMPNO = RESPEMP      WHERE LASTNAME > 'S'
  UNION
  (SELECT EMPNO, LASTNAME, PROJNO      FROM CORPDATA.PROJECT EXCEPTION JOIN CORPDATA.EMPLOYEE            ON EMPNO = RESPEMP      WHERE LASTNAME > 'S');

 

Parent topic:

Joining data from more than one table