Simulate 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 non-matching rows from both tables; left and right.

While DB2^® UDB for iSeries™ does not support full outer join syntax, you can simulate a full outer join by using a left outer join and a right exception join. Suppose you want to find all employees and all projects You want to see those employees that are not currently in charge of a project as well. The following query will return 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');