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:

• 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:

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:

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."

• Tweet
• Share
• Share
• Share

Related Post