7.3 Outer Joins: LEFT, RIGHT, and FULL

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

7.3 Outer Joins: LEFT, RIGHT, and FULL

Key Takeaways Explain This Topic AI

Outer joins return all rows that satisfy the join condition PLUS unmatched rows from one or both tables, populating missing columns with NULL values.

ANSI SQL provides LEFT [OUTER] JOIN (preserves left table), RIGHT [OUTER] JOIN (preserves right table), and FULL [OUTER] JOIN (preserves both tables); the OUTER keyword is optional.

In legacy Oracle syntax, the (+) operator is placed on the column of the table that is deficient in rows (the side that should be padded with NULLs).

Legacy (+) syntax limitations on 1Z0-071: (+) cannot be used in an ANSI ON clause, cannot perform a FULL outer join in a single condition, and cannot be used with OR or IN operators (ORA-01719).

Placing filtering predicates in the ON clause vs. the WHERE clause produces fundamentally different results in outer joins: ON filters during join matching while WHERE filters after row preservation.

Last updated: August 2026

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 NULL s. -- 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

Capability / Feature ANSI SQL:1999 ( LEFT/RIGHT/FULL ) Legacy Oracle (+) Operator
Full Outer Join in Single Query Yes ( FULL OUTER JOIN ) No (Raises ORA-01468 )
Condition in ON vs WHERE Yes (Separates join logic from row filtering) No (All conditions in WHERE )
Support for OR / IN Predicates Yes No (Raises ORA-01719 )
Portability across RDBMS Universal standard (PostgreSQL, MySQL, SQL Server) Proprietary to Oracle Database
Readability High (Clear intent and structure) Prone to operator misplacement