Oracle:Outer Join

Main Page >> Oracle and SQL >> Workbook >> Joins >> Outer Joins

Outer Joins

If a row in one of the tables does not satisfy the join condition, that row ordinarily will not appear in the query's result. For example, department 40 in the table DEPT has no matching employees in the table EMP. Therefore, the result of a join on DEPT and EMP would not include department 40.

If you do want to display those department rows that have no matching employees, use the outer join operator (a plus sign enclosed in parentheses) directly after the column that will generate blanks in the join condition.

As an example, to join the emp and bonus table:

SELECT DEPTNO, EMP.ENAME, EMP.JOB, BONUS.COMM
FROM EMP, BONUS
WHERE EMP.ENAME = BONUS.ENAME (+);

This assumes you have completed the Create table section, where the BONUS table was created.

List the employee names (ename) and department details for all departments, whether they have employees or not:

SELECT DEPT.DEPTNO,DNAME, JOB, ENAME  FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO (+)
ORDER BY DEPT.DEPTNO;

List the department details for all departments with no employees:

SELECT DEPT.DEPTNO,DNAME  
FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO (+)
AND EMP.DEPTNO IS NULL;

Remove the outer join symbol for all the above queries and note what the differences are.

Note, the SQL Standard now includes ANSI joins, which will be introduced later in the database modules.

Next Step

This completes the join section. Now return to the Workbook.