Full Outer Join in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Full Outer Join in Oracle with Examples

In this article, I am going to discuss the Full Outer Join in Oracle with Examples. Please read our previous article where we discussed the Right Outer Join in Oracle with Examples. At the end of this article, you will understand the following pointers.

1. What is Full Outer Join?
2. How to implement Full Outer Join?
3. When do we need to use the Full Outer JOIN?
4. Example to Understand Full Outer Join in Oracle
5. How to retrieve only the non-matching rows from both the left and right table?
6. Joining three Tables in Oracle using Full Outer Join

What is Full Outer Join in Oracle?

The Full Outer Join is used to retrieve all the matching records from both the tables involved in the join as well as all the non-matching records from both the tables. The Un-matching data in such cases will take the null value. The following diagram shows the pictorial representation of Full Outer Join in Oracle.

How to implement Full Outer Join in Oracle?

Please have a look at the following image which shows how to use Full Outer Join in Oracle. Here, you can use either the FULL OUTER JOIN or FULL JOIN keyword to perform Full Outer Join Operation. If you use only the Full JOIN keyword, then it is also going to perform Full Outer Join Operation in Oracle.

When do we need to use the FULL OUTER JOIN?

If you want to fetch all the records from the left-hand side table plus all the records from the right-hand side table then you need to use the Full Outer Join in Oracle. To simplify this, we can say that if you want to retrieve all the matching rows as well as all the non-matching rows from both the tables involved in the join then you need to use Full Outer Join. 

Example to Understand Full Outer Join in Oracle.

Let us understand how to implement Full Outer Join in Oracle with Examples. To understand Full Outer Join, we are going to use the following Company and Candidate tables.

To create and populate the above two tables, please use the below SQL script. 

CREATE TABLE Company
(
    CompanyId INT,
    CompanyName VARCHAR(20)
);

INSERT INTO Company VALUES(1, 'DELL');
INSERT INTO Company VALUES(2, 'HP');
INSERT INTO Company VALUES(3, 'IBM');
INSERT INTO Company VALUES(4, 'Microsoft');

CREATE TABLE Candidate
(
    CandidateId INT,
    FullName VARCHAR(20),
    CompanyId INT
);

INSERT INTO Candidate VALUES(1, 'Ron',1);
INSERT INTO Candidate VALUES(2, 'Pete',2);
INSERT INTO Candidate VALUES(3, 'Steve',3);
INSERT INTO Candidate VALUES(4, 'Smith',NULL);
INSERT INTO Candidate VALUES(5, 'Ravi',1);
INSERT INTO Candidate VALUES(6, 'Raj',3);
INSERT INTO Candidate VALUES(7, 'Kiran',NULL);

Example: Full Outer Join

Our requirement is to write a query, to retrieve CandidateId, FullName, CompanyId, and CompanyName from Company and Candidate tables. The output of the query should be as shown below.

Following is the Query which is an example of Full Outer Join that is joining the Company and Candidate tables and will give the results shown in the above image.

SELECT Cand.CandidateId, 
 Cand.FullName, 
 Cand.CompanyId, 
 Comp.CompanyId, 
 Comp.CompanyName
FROM Candidate Cand
FULL OUTER JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId;

If you look at the output, now we now got 8 rows. That means all the records from the Candidate Table as well as all the records from the Company Table. Instead of using the Full Outer Join keyword, you can also use the Full Join keyword. This will also work as expected as Full Outer Join. The following example uses Full Join Keyword to join both tables.

SELECT Cand.CandidateId, 
 Cand.FullName, 
 Cand.CompanyId, 
 Comp.CompanyId, 
 Comp.CompanyName
FROM Candidate Cand
FULL JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId;

In short, Full Outer Join, or you can say Full Join will return all the records from both left and right tables involved in the join, including the non-matching rows.

How to retrieve only the non-matching rows from both the left and right table?

Here, what we want is, we want to retrieve only the non-matching records from both tables. For example, we want the following result sets when we join the Candidate and Company tables. If you further notice, here all the matching rows are eliminated.

As we already discussed, Full Outer Join in Oracle is used to fetch all the matching records from both the tables as well as all the non-matching records from both tables. In this case, the non-matching records will take NULL value. So, to get the above output what we need to do is, we need to perform Full Outer Join operation, and then in the where condition we need to filter out the records which have NULL values. The following Full Outer Join Query with the where clause does the same.

SELECT Cand.CandidateId, 
 Cand.FullName, 
 Cand.CompanyId, 
 Comp.CompanyId, 
 Comp.CompanyName
FROM Candidate Cand
FULL OUTER JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId
WHERE   Cand.CompanyId IS NULL
OR      Comp.CompanyId IS NULL;

Joining three Tables using Full Outer Join:

Now we will see how to Full Join three tables. Already we have the Company and Candidate tables. Let us introduce the third table i.e. Address table. So, we are going to use the following Address table along with the Company and Candidate tables to perform full outer joining operations between three tables.

Please use the following SQL Script to create and populate the Address table with the required test data.

CREATE TABLE Address
(
    AddressId INT,
    CandidateId INT,
    Country VARCHAR(10),
    State VARCHAR(20),
    City VARCHAR(10)
);

INSERT INTO Address Values (1, 1, 'India', 'Odisha', 'BBSR');
INSERT INTO Address Values (2, 2, 'India', 'Maharashtra', 'Mumbai');
INSERT INTO Address Values (3, 3, 'India', 'Maharashtra', 'Pune');
INSERT INTO Address Values (4, 4, 'India', 'Odisha', 'Cuttack');

The following is the syntax to join three tables in Oracle.

Example: Joining Candidate, Company, and Address tables using Full Outer Join

The following Query Joining Candidate, Company, and Address tables.

SELECT Cand.CandidateId, 
       Cand.FullName, 
       Cand.CompanyId, 
       Comp.CompanyName, 
       Addr.Country, 
       Addr.State, 
       Addr.City 
FROM Candidate Cand 
FULL JOIN Company Comp 
ON Cand.CompanyId = Comp.CompanyId 
FULL JOIN Address Addr 
ON Addr.CandidateId = Cand.CandidateId;

When you execute the above Full Outer Join Query, you will get the below output.

In the next article, I am going to discuss CROSS Join in Oracle with examples. Here, in this article, I try to explain the Full Outer Join in Oracle with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.