Full Outer Join Using (+) Operator in Oracle SQL

Topic Introduction: This tutorial will show Full Outer Join Using (+) Operator in Oracle SQL. As usual for full outer join, we use the ON clause. If we want to use (+) Operator in the Where clause we need to use the Union. Let's see the details.

This is a query example to get 2 tables with all data using the FULL OUTER JOIN and the ON clause.

SELECT e.last_name,

       e.department_id,

       d.department_id,

       d.department_name

  FROM employees  e

       FULL OUTER JOIN departments d ON e.department_id = d.department_id;

This is a query example to get 2 tables with all data using the (+) Operator and use the Where clause.

SELECT e.last_name,

       e.department_id,

       d.department_id,

       d.department_name

  FROM employees e, departments d

 WHERE e.department_id = d.department_id(+)

UNION ALL

SELECT e.last_name,

       e.department_id,

       d.department_id,

       d.department_name

  FROM employees e, departments d

 WHERE e.department_id(+) = d.department_id AND e.department_id IS NULL;

The same data tested by this query

SELECT e.last_name,

       e.department_id,

       d.department_id,

       d.department_name

  FROM employees  e

       FULL OUTER JOIN departments d ON e.department_id = d.department_id

MINUS

(SELECT e.last_name,

        e.department_id,

        d.department_id,

        d.department_name

   FROM employees e, departments d

  WHERE e.department_id = d.department_id(+)

 UNION ALL

 SELECT e.last_name,

        e.department_id,

        d.department_id,

        d.department_name

   FROM employees e, departments d

  WHERE e.department_id(+) = d.department_id AND e.department_id IS NULL);