1. Overview

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

An OUTER JOIN in Oracle retrieves rows from two or more tables, including unmatched rows from one or both tables. This makes it useful when you want to retain all rows from one table while including only matching rows from the other table. Outer joins are particularly helpful in scenarios where you want to include data that may not have corresponding entries in the related table.

• To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.
• To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
• To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.
• You cannot compare a column with a subquery in the WHERE clause of any outer join, regardless which form you specify.

2. Types of outer joins

Oracle supports three types of outer joins:

• LEFT OUTER JOIN: Returns all rows from the left table and the matched rows from the right table. Unmatched rows from the right table are filled with NULL.
• RIGHT OUTER JOIN: Returns all rows from the right table and the matched rows from the left table. Unmatched rows from the left table are filled with NULL.
• FULL OUTER JOIN: Returns all rows when there is a match in either table, and unmatched rows from both tables are filled with NULL. Combines the results of a LEFT OUTER JOIN and a RIGHT OUTER JOIN. It includes:
  • Matched rows from both tables.
  • Unmatched rows from the first table (filled with NULL for the second table’s columns).
  • Unmatched rows from the second table (filled with NULL for the first table’s columns).
• Useful when you need a comprehensive view of data, including all matches and non-matches between two tables.

3. Syntax of Outer Join

LEFT OUTER JOIN

SELECT column_list
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

RIGHT OUTER JOIN

SELECT column_list
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

FULL OUTER JOIN

SELECT column_list
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Syntax (Using + Operator)

In Oracle, the + operator can also be used to perform outer joins. This syntax is older but still widely supported.

LEFT OUTER JOIN Using +

SELECT column_list
FROM table1, table2
WHERE table1.column_name = table2.column_name(+);

RIGHT OUTER JOIN Using +

SELECT column_list
FROM table1, table2
WHERE table1.column_name(+) = table2.column_name;

4. Examples

We’ll use the following tables for this example.

Employee

+-------------+------------+-----------+---------------+
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_ID |
+-------------+------------+-----------+---------------+
| 101         | John       | Doe       | 1             |
+-------------+------------+-----------+---------------+
| 102         | Jane       | Smith     | 2             |
+-------------+------------+-----------+---------------+
| 103         | Sam        | Brown     | 3             |
+-------------+------------+-----------+---------------+
| 104         | Sue        | Green     | 2             |
+-------------+------------+-----------+---------------+
| 105         | Tom        | Day       | 4             |
+-------------+------------+-----------+---------------+

Department

+---------------+-----------------+
| DEPARTMENT_ID | DEPARTMENT_NAME |
+---------------+-----------------+
| 1             | Human Resources |
+---------------+-----------------+
| 2             | Finance         |
+---------------+-----------------+
| 3             | IT              |
+---------------+-----------------+
| 5             | Sales           |
+---------------+-----------------+

LEFT OUTER JOIN Example (ANSI Syntax)

Fetch all employees and their department names (including employees without a department):

SELECT e.employee_id, e.first_name, d.department_name
FROM employee e
LEFT OUTER JOIN department d
ON e.department_id = d.department_id;

Output

+-------------+------------+-----------------+
| EMPLOYEE_ID | FIRST_NAME | DEPARTMENT_NAME |
+-------------+------------+-----------------+
| 101         | John       | Human Resources |
+-------------+------------+-----------------+
| 102         | Jane       | Finance         |
+-------------+------------+-----------------+
| 104         | Sue        | Finance         |
+-------------+------------+-----------------+
| 103         | Sam        | IT              |
+-------------+------------+-----------------+
| 105         | Tom        | (null)          |
+-------------+------------+-----------------+

LEFT OUTER JOIN Example (Using + Operator)

SELECT e.employee_id, e.first_name, d.department_name
FROM employee e, department d
WHERE e.department_id = d.department_id(+);

Output will be the same as earlier.

RIGHT OUTER JOIN Example (ANSI Syntax)

Let us now change LEFT OUTER JOIN to RIGHT OUTER JOIN in previous example.

SELECT e.employee_id, e.first_name, d.department_name
FROM employee e
RIGHT OUTER JOIN department d
ON e.department_id = d.department_id;

+-------------+------------+-----------------+
| EMPLOYEE_ID | FIRST_NAME | DEPARTMENT_NAME |
+-------------+------------+-----------------+
| 101         | John       | Human Resources |
+-------------+------------+-----------------+
| 102         | Jane       | Finance         |
+-------------+------------+-----------------+
| 103         | Sam        | IT              |
+-------------+------------+-----------------+
| 104         | Sue        | Finance         |
+-------------+------------+-----------------+
| (null)      | (null)     | Sales           |
+-------------+------------+-----------------+

As you can see, all departments are returned even with no employees.

Right outer join with + operator

SELECT e.employee_id, e.first_name, d.department_name
FROM employee e, department d
WHERE e.department_id(+) = d.department_id;

The output will still be the same.

5. Full Outer Join

SELECT e.employee_id, e.first_name, d.department_name
FROM employee e
FULL OUTER JOIN department d
ON e.department_id = d.department_id;

+-------------+------------+-----------------+
| EMPLOYEE_ID | FIRST_NAME | DEPARTMENT_NAME |
+-------------+------------+-----------------+
| 101         | John       | Human Resources |
+-------------+------------+-----------------+
| 102         | Jane       | Finance         |
+-------------+------------+-----------------+
| 103         | Sam        | IT              |
+-------------+------------+-----------------+
| 104         | Sue        | Finance         |
+-------------+------------+-----------------+
| 105         | Tom        | (null)          |
+-------------+------------+-----------------+
| (null)      | (null)     | Sales           |
+-------------+------------+-----------------+

• You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
• The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
• If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
• The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.
• You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. For example, the following statement is not valid:

SELECT employee_id, manager_id 
   FROM employees
   WHERE employees.manager_id(+) = employees.employee_id;

However, the following self join is valid:

SELECT e1.employee_id, e1.manager_id, e2.employee_id
   FROM employees e1, employees e2
   WHERE e1.manager_id(+) = e2.employee_id
   ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;

• The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
• A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
• A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.