Home » Oracle SQL » Oracle Full Outer Join with Examples

Oracle Full Outer Join with Examples

We will be learning the usage of FULL OUTER JOIN in Oracle in this tutorial for joining two or more than two tables.

As we know there are three types of Outer Joins, i.e, Left Outer Join, Right Outer Join and Full Outer Join which we use when we want to keep rows from either left, right or both tables in the final output.

FULL OUTER JOIN returns all rows that are present in both tables.

A Full Outer Join is used when we combine both the tables and display all values. If there is a matching value in the other table, then values from both left and table will be shown and in case there is no matching value in other table, then a NULL value is returned.

The OUTER word is optional as Oracle treats both FULL JOIN and FULL OUTER JOIN as same.

Syntax:

The syntax of FULL OUTER JOIN is as follows:

SELECT column_names
FROM Table_1
FULL OUTER JOIN Table_2 ON
    join_condition;

Here,

Table_1 and Table_2 – Table_1 and Table_2 are the tables we are about to join. We assume Table_1 to be the left table and Table_2 to be right.

join_condition – The join condition which is based on the common column having same attribute and datatype between both the tables.

The Full Outer Join Query returns all rows from the both the table including the rows with NULL values in the column used in join condition.

The Venn Diagram of Full Outer Join is as follows:

Oracle FULL OUTER JOIN Examples

We will be creating two sample tables for understanding the FULL OUTER JOIN in Oracle.

CREATE TABLE food_a (
    id INT PRIMARY KEY,
    food VARCHAR2 (50) NOT NULL
);

CREATE TABLE food_b (
    id INT PRIMARY KEY,
    color VARCHAR2 (50) NOT NULL
);

-- insert data in food_a table

INSERT INTO food_a (id, item)
VALUES (1, 'Pizza');

INSERT INTO food_a (id, item)
VALUES (2, 'Burger');

INSERT INTO food_a (id, item)
VALUES (3, 'Soup');

INSERT INTO food_a (id, item)
VALUES (4, 'Pasta');

-- insert data in food_b table

INSERT INTO food_b (id, item)
VALUES (1, 'Pizza');

INSERT INTO food_b (id, item)
VALUES (2, 'Burger');

INSERT INTO food_b (id, item)
VALUES (3, 'Cake');

INSERT INTO food_b (id, item)
VALUES (4, 'Salad');

Result:

We will get two tables food_a and food_b. We can assume food_a to be left and food_b to be right.

Let’s apply the FULL OUTER JOIN query on these tables:

SELECT
    a.id id_a,
    a.item item_a,
    b.id id_b,
    b.item item_b
FROM
    food_a a
FULL OUTER JOIN food_b b ON a.item = b.item;

Result:

Oracle Full Outer Join Example Tables Query

As we can see, the result set contains all rows from both tables and shows NULL values in absence of matching values in the other table.

This can be illustrated in the following Venn Diagram:

For the coming examples, we will be using Employee and Department table from HR schema

1. Oracle Full Join Example with HR Schema

We use the following query for joining the Employees and Departments tables and return all rows:

SELECT first_name, last_name, 
       department_name, salary
FROM employees e
FULL OUTER JOIN departments d 
ON e.department_id= d.department_id;

Result:

The query fetches 123 results which is greater than the number of rows in Employee table which is 107. If we look, closely at the result data set then we will find that it also lists the departments from DEPARTMENT table, which have no employees assigned to it in the employees table. The columns from employees table in this case shows NULL values.

2. Oracle FULL OUTER JOIN for finding unique rows in each table

Suppose we want to find the rows which are unique to each table and have no matching row in other table.

In this case, we want to find employees which have not been assigned any department and from the department table we want to find the departments in which no employees have been assigned. For this case, we use the following query.

SELECT first_name, last_name, 
       department_name, salary
FROM employees e
FULL OUTER JOIN departments d 
ON e.department_id= d.department_id
WHERE e.employee_id IS NULL or d.department_id IS NULL;

Result: