How to use FULL OUTER JOIN in SQL SERVER with example

Introduction: In this article i am going to explain the Use of FULL OUTER JOIN in SQL SERVER using an example.

Description: The FULL OUTER JOIN is the combination of left outer and right outer join and it returns all rows  (matched or unmatched) from the left table (Table1) and from the right table (Table2) participating in JOIN. It returns null when there is no match found. In previous articles i explained Use of SELF JOIN in SQL SERVER with example and CROSS JOIN or CARTESIAN JOIN and Example to explain Transaction in Sql server and What is cursor? Advantages and disadvantages,when to use and example to explain and Remove duplicate records from Sql Server and Create Sql server database script and Create database from that script and Get column values as comma separated list in sql server | Convert column values to row 

Syntax for FULL OUTER JOIN

SELECT column_name(s) FROM Table1
FULL OUTER JOIN Table2
ON Table1.column_name=Table2.column_name;

Implementation: Let's understand by an example. In the following example using a full outer join, we can find all employees regardless of whether they worked in any department and all departments regardless of whether they have any employee working in.
  • Create a table for storing the employee details using the script below.

CREATE TABLE [dbo].[EmpDetails]
(
                [EmpId] [int] IDENTITY(1,1) NOT NULL,
                [EmpName] [varchar](50)NULL,
                [Salary] [int] NULL,
                [DeptIdFk] [int] NULL
)

INSERT INTO EmpDetails(EmpName,Salary,DeptIdFk) VALUES('Lalit',35000,2)
INSERT INTO EmpDetails(EmpName,Salary,DeptIdFk) VALUES('Amit',18000,4)
INSERT INTO EmpDetails(EmpName,Salary,DeptIdFk) VALUES('Raman',20000,3)
INSERT INTO EmpDetails(EmpName,Salary,DeptIdFk) VALUES('Sameer',21000,NULL)
INSERT INTO EmpDetails(EmpName,Salary,DeptIdFk) VALUES('Harjeet',12000,1)
INSERT INTO EmpDetails(EmpName,Salary,DeptIdFk) VALUES('Aalok',15000,NULL)
INSERT INTO EmpDetails(EmpName,Salary,DeptIdFk) VALUES('Anuj',16000,1)

It will look like as shown in image below:

full outer join example in sql server

  • Create a table for storing the departments using the script below.

CREATE TABLE [dbo].[Tb_Dept]
(
                [DEPT_ID] [bigint] IDENTITY(1,1) NOT NULL,
                [DEPT_NAME] [varchar](50)NULL
)
INSERT INTO Tb_Dept (DEPT_NAME) VALUES ('HR')
INSERT INTO Tb_Dept (DEPT_NAME) VALUES ('IT')
INSERT INTO Tb_Dept (DEPT_NAME) VALUES ('SALES')
INSERT INTO Tb_Dept (DEPT_NAME) VALUES ('ACCOUNTS')
INSERT INTO Tb_Dept (DEPT_NAME) VALUES ('MANAGEMENT')

It will look like as shown in image below:

full outer join example in sql server

Now using the below mentioned FULL OUTER JOIN we can get the details of all employees regardless of whether they worked in any department and all departments regardless of whether they have any employee working in.

SELECT EmpDetails.EmpName,EmpDetails.Salary,Tb_Dept.dept_name FROM EmpDetails
FULL OUTER JOIN dbo.Tb_Dept
ON EmpDetails.DeptIdFk=Tb_Dept.DEPT_ID

Result of FULL OUTER JOIN will be like as shown in image below:

full outer join example in sql server

Notice that corresponding to Employee "Sameer" and "Aalok" there is no department. Similarly there is no employee working in department "MANAGEMANENT" so FULL OUTER JOIN places NULL for them as shown in image above.

Now over to you:
" I hope you have got the use of FULL OUTER JOIN in SQL SERVER and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

4 comments

Click here for comments
January 10, 2014 ×

Good Post, Thanks

Reply
avatar
January 10, 2014 ×

Hello Switin Kotian..thanks for appreciating my work..stay connected and keep reading..

Reply
avatar
Unknown
admin
March 22, 2014 ×

EXCELLENT LALIT..,

Reply
avatar
March 23, 2014 ×

Hi Devarajan..thanks for your appreciations..Stay connected and keep reading:)

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..