An outer join is required when rows that would normally be eliminated by an inner join need to be preserved.
Unmatched rows are kept in the result, with NULLs filled in for the missing side's columns.
The diagram below shows which rows each outer join type keeps.
The three types of outer join
LEFT OUTER JOIN - returns the rows an inner join would return, plus all rows stored in the leftmost table of the join.
RIGHT OUTER JOIN - returns the rows an inner join would return, plus all rows stored in the rightmost table of the join.
FULL OUTER JOIN - returns the rows an inner join would return, plus all rows stored in both tables of the join.
Example:-
-- All employees, with department name where a match exists
SELECT E.LASTNAME, D.DEPTNAME
FROM EMPLOYEE E LEFT OUTER JOIN DEPARTMENT D
ON E.WORKDEPT = D.DEPTNO;
-- All departments, even those with no employees
SELECT D.DEPTNAME, E.LASTNAME
FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D
ON E.WORKDEPT = D.DEPTNO;
Restrictions on outer joins in DB2
The VALUE or COALESCE function can be used only with a FULL OUTER join.
Only '=' can be used as the comparison operator in a FULL OUTER join.
Multiple join conditions can be coded with AND. 'OR' and 'NOT' are not allowed in a FULL OUTER join.
Mainframe Tutorial
JCL Tutorial
Introduction
Mainframe Interview Q&A
Mainframe Quiz
Mainframe Memes
Contact us
About us
Backward
