Oracle FULL OUTER JOIN

What is full outer join in Oracle?

A full outer join performs a join between two tables that returns the results of an INNER join as well as the results of a left and right outer join.

Syntax:

SELECT table1.column, table2.column
FROM table1
FULL OUTER JOIN table2
ON (table1.column = table2.column);

Pictorial presentation of Oracle Full Outer Join

Example-1: Oracle FULL OUTER JOIN

This following query retrieves all rows in the employees table, even if there is no match in the departments table. It also retrieves all rows in the departments table, even if there is no match in the employees table.

Sample table: employees

Sample table: departments

SELECT a.first_name, b.department_id, b.department_name
FROM employees a FULL OUTER JOIN departments b
ON (a.department_id = b.department_id); 

Sample Output:

FIRST_NAME           DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- --------------------
Steven                          90 Executive
Neena                           90 Executive
Lex                             90 Executive
Alexander                       60 IT
Bruce                           60 IT
David                           60 IT
Valli                           60 IT
Diana                           60 IT
.....................................
.....................................
Shelley                        110 Accounting
William                        110 Accounting
                               220 NOC
                               170 Manufacturing
                               240 Government Sales
                               210 IT Support
                               160 Benefits
.....................................

Example-2: Full Outer Join with Where clause

This following query retrieves all rows in the employees table, even if there is no match in the departments table. It also retrieves all rows in the departments table, even if there is no match in the employees table, and finally, the result filtered for those departments which name starts with the letter 'P'.

Sample table: employees

Sample table: departments

SELECT a.first_name, b.department_id, b.department_name
FROM employees a FULL OUTER JOIN departments b
ON (a.department_id = b.department_id) 
WHERE b.department_name LIKE 'P%';

Sample Output:

FIRST_NAME           DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- --------------------
Den                             30 Purchasing
Alexander                       30 Purchasing
Shelli                          30 Purchasing
Sigal                           30 Purchasing
Guy                             30 Purchasing
Karen                           30 Purchasing
Hermann                         70 Public Relations
                               270 Payroll

FULL OUTER JOIN: SQL and other Relational Databases

• SQL FULL OUTER JOIN
• PostgreSQL FULL OUTER JOIN

Previous: RIGHT OUTER JOIN
Next: SELF JOIN