Oracle Full Outer Join

Introduction to Oracle Full Outer Join

Oracle Full Outer Join is a clause used in Oracle database to join two or more tables based on a join condition which returns all the columns with rows in the LEFT-HAND table as well as all the columns with rows in the RIGHT-HAND table with their values. When the join condition is met but in case if the join condition is not met by one of the rows of either table then the columns of that table will return with NULL values in the final result set.

Syntax:

SELECT column/columns
FROM Left_table
FULL OUTER JOIN Right_table
ON join_condition;

Parameters:

• Left_table: The table on the left side of the FULL OUTER JOIN condition.
• Right_table: The table on the Right Side of the FULL OUTER JOIN condition.
• join_condition: The condition based on which the result set will be retrieved from the database.

How does Full Outer Join Works?

• It is important to know actually how the full outer join works in Oracle. If we look at the syntax we will see that there are two tables one is called the left _table or T1 and the other as Right_table or T2. When the FULL OUTER JOIN query is executed in the database and if the rows from both T1 and T2 meet the join condition specified in the query then the query will return columns of both rows in the result set. We can say that the row in the T1 table matches the row in the T2 table. We can also write FULL OUTER JOIN as FULL JOIN, both mean the same.
• The next case can be that suppose the row in the T2 table does not have any row in T1 table which matches the join condition provided in the query. In that case, the full outer join will return columns from T2 table with null values for the columns in the T1 table.
• Another case can be the vice-versa, Let us suppose that the T1 table does not have any row in the T2 table which matches the join condition provided in the query. In that case, the full outer join will return columns from T1 table and null values in the columns of T2 table.

Examples of Oracle Full Outer Join

Given below are the examples:

Example #1

In this example, we will try to extract data from two tables named Employee and Vehicle based on the join condition that both of them have the same value in the employee id column. So as we discussed earlier the result set will be based on the join condition.

Code:

SELECT
m.NAME,
v.VEHICLE_NAME
FROM
employee m
FULL OUTER JOIN vehicle v ON m.EMPLOYEE_ID = v.EMPLOYEE_ID
ORDER BY
m.NAME;

In the above query if we see the Left-hand table is an employee and the Right-hand table is a vehicle. So as we have discussed in the earlier section of this article that the rows of the two columns which are specified in the SELECT statement satisfy the join condition will show their actual value in the result set and the rows of the columns present in the vehicle table which do not satisfy the join condition will have null values. So, let us run the query in Oracle SQL developer and look at the output.

Output:

If we see the output, It has a mix of values as well as NULL values. The rows of the vehicle table which did not satisfy the join condition have returned as NULL values.

Example #2

• In this example, we are going to use FULL OUTER JOIN to find employees who do not have any Vehicle assigned to them.
• To execute this example we will have to modify the query as in earlier example if we see that the result set contained both NULL and NOT NULL values. In this query we are going to use a WHERE condition in the query so that the result set is filtered with only the employee names that do not have any vehicle assigned.

Code:

SELECT
m.NAME,
v.VEHICLE_NAME
FROM
employee m
FULL OUTER JOIN vehicle v ON m.EMPLOYEE_ID = v.EMPLOYEE_ID
WHERE v.VEHICLE_NAME IS NULL
ORDER BY
m.NAME;

If we look at the query above we can see that there is a WHERE condition added after the join condition. The WHERE condition selects only those rows where the vehicle name is NULL. Let us execute the query in Oracle SQL developer.

Output:

In the above output, we can see the result only contains the employee names with no assigned vehicle.

Example #3

• In this example, we are going to use FULL OUTER JOIN to find the Vehicles which are not assigned to any employee.
• To execute this condition we will just modify the WHERE condition of the previous example and in place of the Vehicle name we are going to use Employee name.

Code:

SELECT
m.NAME,
v.VEHICLE_NAME
FROM
employee m
FULL OUTER JOIN vehicle v ON m.EMPLOYEE_ID = v.EMPLOYEE_ID
WHERE m.NAME IS NULL
ORDER BY
m.NAME;

As we can see we have just changed the column name in the WHERE condition. Let us run the query in SQL developer and check the output.

Output:

As we can see that the output contains the vehicles which do not have an employee assigned to them.

Conclusion

In this article, we have discussed the definition of the FULL OUTER JOIN and also about the working of the same. Later on, we also saw some examples and executed them in the SQL developer to get an understanding of how to use it in the database.

This is a guide to Oracle Full Outer Join. Here we discuss the introduction, examples and how does Full Outer Join Works? You may also have a look at the following articles to learn more –

1. MySQL Outer Join
2. Types of Joins in SQL Server
3. Table in MySQL
4. SELECT in MySQL
5. OUTER Join in Oracle | Examples