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 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');