7.3 Outer Joins: LEFT, RIGHT, and FULL

7.3 Outer Joins: LEFT, RIGHT, and FULL

An inner join returns only records that find a matching counterpart in the joined table. However, real-world reporting often requires preserving records that lack a matching counterpart—such as listing all departments including those with zero assigned employees, or listing all employees including newly hired staff not yet assigned to a department.

To fulfill this requirement, relational databases provide outer joins. An outer join preserves rows from one or both tables even when no match exists, padding the missing columns with NULL values (a process called NULL extension).

Oracle SQL supports both modern ANSI/ISO SQL:1999 Outer Join syntax (LEFT, RIGHT, and FULL OUTER JOIN) and the Legacy Oracle outer join operator (+).

---

Outer Join Mechanics & NULL Extension

+-----------------------------------------------------------------------------------+
|                             OUTER JOIN ROW PRESERVATION                           |
|                                                                                   |
|   EMPLOYEES TABLE                                 DEPARTMENTS TABLE               |
|   +--------+-----------+---------+                +---------+------------------+  |
|   | EMP_ID | LAST_NAME | DEPT_ID |                | DEPT_ID | DEPT_NAME        |  |
|   +--------+-----------+---------+                +---------+------------------+  |
|   | 100    | King      | 90      |--------------->| 90      | Executive        |  |
|   | 101    | Kochhar   | 90      |--------------->| 90      | Executive        |  |
|   | 178    | Grant     | NULL    | (No match)     | 190     | Contracting      |  |
|   +--------+-----------+---------+                +---------+------------------+  |
|                                                             |                     |
|                                                             v (No employees)      |
|                                                                                   |
|   INNER JOIN: Returns King (90) and Kochhar (90) only.                            |
|   LEFT OUTER JOIN: Returns King (90), Kochhar (90), AND Grant (DEPT = NULL).      |
|   RIGHT OUTER JOIN: Returns King (90), Kochhar (90), AND Contracting (EMP = NULL).|
|   FULL OUTER JOIN: Returns King, Kochhar, Grant, AND Contracting.                 |
+-----------------------------------------------------------------------------------+

---

ANSI SQL Outer Join Syntax

ANSI SQL:1999 defines three types of outer joins. In all three forms, the keyword OUTER is optional (e.g., LEFT JOIN is completely synonymous with LEFT OUTER JOIN).

1. LEFT OUTER JOIN

Returns all rows from the left table (the first table listed) and matching rows from the right table. If a left-table row has no match, right-table columns are populated with NULL.

-- Lists ALL employees, including Grant who has no department
SELECT 
    e.employee_id,
    e.last_name,
    e.department_id AS emp_dept,
    d.department_id AS dept_dept,
    d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id)
ORDER BY e.employee_id;

2. RIGHT OUTER JOIN

Returns all rows from the right table (the second table listed) and matching rows from the left table. If a right-table row has no match (such as a department with no staff), left-table columns are populated with NULL.

-- Lists ALL departments, including Contracting which has no employees
SELECT 
    e.employee_id,
    e.last_name,
    d.department_id,
    d.department_name
FROM employees e
RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id)
ORDER BY d.department_id;

Equivalence Rule: Any LEFT OUTER JOIN can be rewritten as a RIGHT OUTER JOIN simply by reversing the order of the tables in the FROM clause: FROM A LEFT JOIN B $\equiv$ FROM B RIGHT JOIN A

3. FULL OUTER JOIN

Returns all matching rows, PLUS all unmatched rows from the left table, PLUS all unmatched rows from the right table. Any unmatched side is padded with NULLs.

-- Lists ALL employees AND ALL departments simultaneously
SELECT 
    e.employee_id,
    e.last_name,
    d.department_id,
    d.department_name
FROM employees e
FULL OUTER JOIN departments d ON (e.department_id = d.department_id)
ORDER BY e.employee_id, d.department_id;

---

Legacy Oracle Outer Join Syntax: The (+) Operator

Before Oracle supported ANSI SQL:1999 join syntax, outer joins were written using the proprietary (+) operator in the WHERE clause.

The Fundamental Placement Rule of (+)

The (+) Placement Rule: Place the (+) operator on the column of the table that is deficient in rows (the side that lacks data and needs NULL values generated).

+-----------------------------------------------------------------------------------+
|                           LEGACY (+) OPERATOR PLACEMENT                           |
|                                                                                   |
|  Goal: Preserve ALL EMPLOYEES (Left Join)                                         |
|  Deficient Table: DEPARTMENTS lacks matching row for employee with NULL dept.     |
|  Syntax: WHERE employees.department_id = departments.department_id(+)             |
|                                                                                   |
|  Goal: Preserve ALL DEPARTMENTS (Right Join)                                      |
|  Deficient Table: EMPLOYEES lacks matching rows for empty departments.            |
|  Syntax: WHERE employees.department_id(+) = departments.department_id             |
+-----------------------------------------------------------------------------------+

Examples: ANSI vs. Legacy Equivalence

-- LEFT OUTER JOIN Equivalence:
-- ANSI Syntax:
SELECT e.last_name, d.department_name
FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

-- Legacy Oracle (+) Syntax:
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+); -- (+) on departments to preserve employees


-- RIGHT OUTER JOIN Equivalence:
-- ANSI Syntax:
SELECT e.last_name, d.department_name
FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

-- Legacy Oracle (+) Syntax:
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id; -- (+) on employees to preserve departments

---

Strict 1Z0-071 Rules and Restrictions for (+)

The 1Z0-071 exam rigorously tests the syntactic and semantic limitations of the legacy (+) operator:

+-----------------------------------------------------------------------------------+
|                         LEGACY (+) OPERATOR RESTRICTIONS                          |
+------------------------------------+----------------------------------------------+
| Restriction                        | Error Code & Consequence                     |
+------------------------------------+----------------------------------------------+
| No FULL OUTER JOIN in single cond  | Placing (+) on both sides (col1(+) = col2(+))|
|                                    | raises ORA-01468.                            |
+------------------------------------+----------------------------------------------+
| Prohibited in ANSI ON clause       | Mixing (+) with ANSI JOIN syntax raises      |
|                                    | ORA-25156.                                   |
+------------------------------------+----------------------------------------------+
| Prohibited with OR / IN operators  | Using (+) with OR or IN raises               |
|                                    | ORA-01719.                                   |
+------------------------------------+----------------------------------------------+
| Must apply to all join conditions  | If joining on multiple columns, (+) must be  |
|                                    | attached to all conditions for that table.   |
+------------------------------------+----------------------------------------------+

1. No Direct Full Outer Join (ORA-01468)

You cannot place (+) on both sides of a comparison:

-- INVALID: Attempting full outer join with (+)
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id(+);
-- FAILS: ORA-01468: a predicate may reference only one outer-joined table

Note: In legacy syntax, a full outer join required a UNION between a left outer join query and a right outer join query.

2. Prohibited with OR or IN (ORA-01719)

-- INVALID: Using (+) in an OR condition
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
   OR e.salary > 10000;
-- FAILS: ORA-01719: outer join operator (+) not allowed in operand of OR or IN

3. Prohibited in ANSI ON Clause (ORA-25156)

-- INVALID: Mixing ANSI JOIN with legacy (+)
SELECT e.last_name, d.department_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id(+));
-- FAILS: ORA-25156: old style outer join cannot be used with ANSI joins

---

Filtering in Outer Joins: ON Clause vs. WHERE Clause

A critical conceptual topic on the 1Z0-071 exam is the difference between placing a filter predicate in the ON clause versus placing it in the WHERE clause during an outer join.

Scenario 1: Filtering in the ON Clause

When a condition is placed in the ON clause, it is evaluated during the join process. Unmatched preserved rows are still returned:

-- Filter in ON clause
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d 
  ON (e.department_id = d.department_id AND d.department_name = 'Marketing');

• Result: Returns all 107 employees. Employees in the Marketing department display Marketing details; all other employees display NULL for department_name but remain in the output.

Scenario 2: Filtering in the WHERE Clause

When a condition is placed in the WHERE clause, it is evaluated after the outer join is completed. This discards rows failing the condition:

-- Filter in WHERE clause
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d 
  ON (e.department_id = d.department_id)
WHERE d.department_name = 'Marketing';

• Result: Returns only the 2 employees in Marketing. Unmatched employees (and employees in other departments) have d.department_name = NULL, so the WHERE clause eliminates them, effectively degrading the outer join into an inner join!

---

ANSI Outer Joins vs. Legacy Oracle (+) Comparison