Use FULL OUTER JOIN in SQL SERVER with example

Full outer join is the combine result of both left outer and right outer join and it returns all  rows  (matched or unmatched) from the left table (Table1) as well as from the right table (Table2) participating in JOIN. It returns null when there is no match found. 

Syntax for FULL OUTER JOIN

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

 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 Emp  employee details using the script below.

CREATE TABLE [dbo].[Emp]

(

                [Emp_Id_pk] [int] IDENTITY(1,1) NOT NULL,

                [Emp_Name] [varchar](50)NULL,

                [Salary] [int] NULL,

                [Dept_Id] [int] NULL

)

 Insert Some Data in this table using Insert query.It will look like as shown in image below:

Create a another table “Dept_table” for  departments using the script below.

CREATE TABLE [dbo].[Dept_Table]

(

                [DEPT_ID_Pk] [bigint] IDENTITY(1,1) NOT NULL,

                [DEPT_NAME] [varchar](50)NULL

)

Insert some data in this table using insert command. 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 Emp.Emp_Name,Emp.Salary,Dept_table.dept_name FROM Emp

FULL OUTER JOIN dbo.Dept_table

ON Emp.Dept_Id=Dept_table.DEPT_ID_Pk

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

Note: For Employee "abc” 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.