SQL Server Full Outer Join

Back to: SQL Server Tutorial For Beginners and Professionals

SQL Server Full Outer Join with real-time Examples

In this article, I am going to discuss the SQL Server Full Outer Join with one real-time example. Please read our previous article where we discussed the SQL Server Right Outer Join with an example. The SQL Server 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. 

Let us understand SQL Server Right Outer Join with an example.

To understand the SQL Server Full Outer Join, here we are going to use the below two tables i.e. the 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 which is referencing to 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

If you want to select all the records from the left-hand side Table i.e. from the Candidate Table plus all the records from the right-hand side table i.e. the Company Table, then you need to use the SQL Server Full Outer Join.

Example:

The following SQL Script is an example of SQL Server Full Outer Join which will join the Candidate and Company Tables.

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

When you execute the above Full Outer Join Query, then it will give you the following output.

If you look at the above 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.

Note:

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. 

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, the SQL Server Full Outer Join, or you can say Full Join will return all the records from both the left and right tables involved in the join, including the non-matching rows. 

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