Full Outer Join in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Full Outer Join in SQL Server with Examples

In this article, I am going to discuss the Full Outer Join in SQL Server with Examples. Please read our previous article where we discussed the SQL Server Right Outer Join with an example. 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 SQL Server Full Outer Join
5. How to retrieve only the non-matching rows from both the left and right table?
6. Joining three Tables in SQL Server using Full Outer Join

What is Full Outer Join in SQL Server?

The Full Outer Join in SQL Server 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 SQL Server.

How to implement Full Outer Join in SQL Server?

Please have a look at the following image which shows the syntax of SQL Server Full Outer Join. Here, you can use either the Full Outer Join or Full JOIN keyword to perform Full Join. If you use only the Full JOIN keyword, then it is also going to perform Full Outer Join.

When do we need to use the Full Outer JOIN?

If you want to select 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 SQL Server Full Outer Join. 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 in SQL Server. 

Example to Understand SQL Server Full Outer Join.

Let us understand how to implement Full Outer Join in SQL Server 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. Please note the CompanyId column of the Candidate Table is the foreign key column that is referencing the CompanyId column of the Company Table.

CREATE TABLE Company
(
    CompanyId TinyInt Identity Primary Key,
    CompanyName Nvarchar(50) NULL
)
GO
INSERT Company VALUES('DELL')
INSERT Company VALUES('HP')
INSERT Company VALUES('IBM')
INSERT Company VALUES('Microsoft')
GO
CREATE TABLE Candidate
(
    CandidateId tinyint identity primary key,
    FullName nvarchar(50) NULL,
    CompanyId tinyint REFERENCES Company(CompanyId)
)
GO
INSERT Candidate VALUES('Ron',1)
INSERT Candidate VALUES('Pete',2)
INSERT Candidate VALUES('Steve',3)
INSERT Candidate VALUES('Steve',NULL)
INSERT Candidate VALUES('Ravi',1)
INSERT Candidate VALUES('Raj',3)
INSERT Candidate VALUES('Kiran',NULL)
GO

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. That means the SQL Server Full Outer Join or Full Join means the same. 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, the Full Outer Join is used to fetch all the matching records from both the tables as well as all the non-matching records from both the table. 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 OuterJoin Query with the where clause does the same.

SELECT  Cand.CandidateId, 
 Cand.FullName, 
 Cand.CompanyId, 
 Comp.CompanyId, 
 Comp.CompanyName
FROM	Candidate Cand
FULL  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. the 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 IDENTITY PRIMARY KEY,
    CandidateId tinyint REFERENCES Company(CompanyId),
    Country VARCHAR(50),
    State VARCHAR(50),
    City VARCHAR(50),
)
GO

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

The following is the syntax to join three tables in SQL Server.

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 Self Join in SQL Server with an example. Here, in this article, I try to explain the SQL Server Full Outer Join 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.