An outer join extends the result of an inner join. An outer join returns all rows that satisfy the join condition (matching rows from both tables) and also returns non-matching rows from either/both table(s). Oracle provides the (+) operator to perform outer joins. The outer join concept is simple. The (+) operator is placed in the join condition in the opposite side of the table from which all the rows are to be selected.

Q : Find the employees name, designation and their manager’s name, and designation?

Ans:

SELECT
    e.Ename  "Employees",
    e.Job    "Employee Designation",
    m.Ename  "Managers",
    m.Job    "Manager Designation"
  FROM Emp e, Emp m
 WHERE e.Mgr = m.Empno;

Employees  Employee Designation      Managers   Manager Designation
---------- ------------------------- ---------- -------------------------
FORD       ANALYST                   JONES      MANAGER
SCOTT      ANALYST                   JONES      MANAGER
ALLEN      SALESMAN                  BLAKE      MANAGER
JAMES      CLERK                     BLAKE      MANAGER
TURNER     SALESMAN                  BLAKE      MANAGER
MARTIN     SALESMAN                  BLAKE      MANAGER
WARD       SALESMAN                  BLAKE      MANAGER
MILLER     CLERK                     CLARK      MANAGER
ADAMS      CLERK                     SCOTT      ANALYST
BLAKE      MANAGER                   KING       PRESIDENT
CLARK      MANAGER                   KING       PRESIDENT
JONES      MANAGER                   KING       PRESIDENT
SMITH      CLERK                     FORD       ANALYST

13 rows selected.

In above example, you get details for 13 employees. However, Emp table has 14 rows. So, where is the missing row? Which row is missing?

Clearly, it’s “KING” who is missing. Why the row is not part of the output?

Look at the Emp table data and you could see “Mgr” value for “KING” is NULL. ” WHERE e.Mgr = m.Empno ” conditon in the query returns “UNKNOWN” and hence this row is not part of the output.

Note

Any operation with NULL will result “UNKNOWN”. You must handle NULL with IS NULL or IS NOT NULL operators in Oracle database.

Explaining Outer Joins through Venn Diagrams

Conceptually Outer joins can be of following types.

• Left Outer Join
• Right Outer Join
• Full Outer Join

Left Outer Joins in Oracle

Left outer join is also referred as Left Join. A Left Join returns all records from the left side table <Table A>, and only the matched records from the right table <Table B>. The result for all columns from right side table is NULL for the non-matching rows.

Q : Find the employees name, designation and their manager’s name, and designation for all employees?

Ans:

SELECT
    e.Ename  "Employees",
    e.Job    "Employee Designation",
    m.Ename  "Managers",
    m.Job    "Manager Designation"
  FROM Emp e, Emp m
 WHERE e.Mgr = m.Empno (+);

Employees  Employee Designation      Managers   Manager Designation
---------- ------------------------- ---------- -------------------------
FORD       ANALYST                   JONES      MANAGER
SCOTT      ANALYST                   JONES      MANAGER
ALLEN      SALESMAN                  BLAKE      MANAGER
JAMES      CLERK                     BLAKE      MANAGER
TURNER     SALESMAN                  BLAKE      MANAGER
MARTIN     SALESMAN                  BLAKE      MANAGER
WARD       SALESMAN                  BLAKE      MANAGER
MILLER     CLERK                     CLARK      MANAGER
ADAMS      CLERK                     SCOTT      ANALYST
BLAKE      MANAGER                   KING       PRESIDENT
CLARK      MANAGER                   KING       PRESIDENT
JONES      MANAGER                   KING       PRESIDENT
SMITH      CLERK                     FORD       ANALYST
KING       PRESIDENT

14 rows selected.

Use of NULL handling functions in any outer join renders the result set add more meaning to the output.

SELECT
    e.Ename                       "Employees",
    e.Job                         "Employee Designation",
    nvl(m.Ename, 'No Manager')    "Managers",
    nvl(m.Job, 'BOSS')            "Manager Designation"
  FROM Emp e, Emp m
 WHERE e.Mgr = m.Empno (+);

Employees  Employee Designation      Managers   Manager Designation
---------- ------------------------- ---------- -------------------------
FORD       ANALYST                   JONES      MANAGER
SCOTT      ANALYST                   JONES      MANAGER
ALLEN      SALESMAN                  BLAKE      MANAGER
JAMES      CLERK                     BLAKE      MANAGER
TURNER     SALESMAN                  BLAKE      MANAGER
MARTIN     SALESMAN                  BLAKE      MANAGER
WARD       SALESMAN                  BLAKE      MANAGER
MILLER     CLERK                     CLARK      MANAGER
ADAMS      CLERK                     SCOTT      ANALYST
BLAKE      MANAGER                   KING       PRESIDENT
CLARK      MANAGER                   KING       PRESIDENT
JONES      MANAGER                   KING       PRESIDENT
SMITH      CLERK                     FORD       ANALYST
KING       PRESIDENT                 No Manager BOSS

14 rows selected.

Right Outer Joins in Oracle

Right outer join is also referred as Right Join. A Right Join returns all records from the right side table <Table B>, and only the matched records from the left table <Table A>. The result for all columns from left side table is NULL for the non-matching rows.

Q : Get the details of all departments and employees working in each department?

Ans:

SELECT 
    NVL(e.Ename, 'No Employee') AS "Employee Name", 
    D.Deptno, 
    D.Dname, 
    D.Loc Location
  FROM Emp E, Dept D
 WHERE E.Deptno (+) = D.Deptno
ORDER BY D.Deptno;

Employee Name       DEPTNO DNAME          LOCATION
-------------   ---------- -------------- -------------
CLARK                   10 ACCOUNTING     NEW YORK
MILLER                  10 ACCOUNTING     NEW YORK
KING                    10 ACCOUNTING     NEW YORK
FORD                    20 RESEARCH       DALLAS
SCOTT                   20 RESEARCH       DALLAS
JONES                   20 RESEARCH       DALLAS
SMITH                   20 RESEARCH       DALLAS
ADAMS                   20 RESEARCH       DALLAS
WARD                    30 SALES          CHICAGO
MARTIN                  30 SALES          CHICAGO
TURNER                  30 SALES          CHICAGO
JAMES                   30 SALES          CHICAGO
ALLEN                   30 SALES          CHICAGO
BLAKE                   30 SALES          CHICAGO
No Employee             40 OPERATIONS     BOSTON

15 rows selected.

Full Outer Joins in Oracle

Full outer join is also referred as Full Join. A Full Join returns all matching and non-matching rows from both tables. This means, full join returns all matching rows, all non-matching rows from left table as well as all non-matching rows from right table.

Oracle (+) operator can not be used on both side in a join condition to perform a full outer join. If you place (+) on both sides in a WHERE clause condition, you get “ORA-01468: a predicate may reference only one outer-joined table” error.

SELECT 
    NVL(e.Ename, 'No Employee') AS "Employee Name", 
    D.Deptno, 
    D.Dname, 
    D.Loc Location
  FROM Emp E, Dept D
 WHERE E.Deptno (+) = D.Deptno(+);
                    *
ERROR at line 7:
ORA-01468: a predicate may reference only one outer-joined table

You can not even use (+) operator on two conditions, each for one table in the same query. See below example which produces error “ORA-01416: two tables cannot be outer-joined to each other”.

SELECT 
    NVL(e.Ename, 'No Employee') AS "Employee Name", 
    D.Deptno, 
    D.Dname, 
    D.Loc Location
  FROM Emp E, Dept D
 WHERE E.Deptno = D.Deptno(+)
   AND E.Deptno(+) = D.Deptno;
             *
ERROR at line 3:
ORA-01416: two tables cannot be outer-joined to each other

How do you perform outer join in Oracle using (+) operator?

To enable users run full outer joins on tables, Oracle complies with ANSI SQL standards and supports Full Outer Join coding syntax. Learn the ANSI join operations supported by Oracle in our next chapter.