T-SQL - FULL OUTER JOIN

The T-SQL (Transact-SQL) FULL OUTER JOIN keyword (or sometimes called FULL JOIN) is used to combine column values of two tables based on the match between the columns and returns all rows of the both tables. If there is no match in any table, the result table will contain NULL value.

Syntax

The syntax for using FULL OUTER JOIN keyword in T-SQL (Transact-SQL) is given below:

SELECT table1.column1, table1.column2, table2.column1, table2.column2, ...
FROM table1
FULL OUTER JOIN table2
ON table1.matching_column = table2.matching_column;

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

Table 1: Employee table

Table 2: Contact_Info table

• To full outer join Employee and Contact_Info tables based on matching column EmpID, the query is given below. This will fetch Name and Age columns from Employee table and Address column from Contact_Info table.

  SELECT Employee.Name, Employee.Age, Contact_Info.Address 
  FROM Employee
  FULL OUTER JOIN Contact_Info
  ON Employee.EmpID = Contact_Info.EmpID;
  

  
  

  This will produce the result as shown below:

  Name	Age	Address
  John	25
  Marry	24	Brooklyn, New York, USA
  Jo	27	Grenelle, Paris, France
  Kim	30	Geuzenveld, Amsterdam, Netherlands
  Ramesh	28
  Huang	28	Yizhuangzhen, Beijing, China
  		Yishun, Singapore
  		Koto City, Tokyo, Japan

• To fetch all fields of a table, table.* keyword is used, for example - to fetch all fields of the Employee table, Employee.* is used in the below query:

  SELECT Employee.*, Contact_Info.Address 
  FROM Employee
  FULL OUTER JOIN Contact_Info
  ON Employee.EmpID = Contact_Info.EmpID;
  

  
  

  This result of the following code will be:

  EmpID	Name	City	Age	Salary	Address
  1	John	London	25	3000
  2	Marry	New York	24	2750	Brooklyn, New York, USA
  3	Jo	Paris	27	2800	Grenelle, Paris, France
  4	Kim	Amsterdam	30	3100	Geuzenveld, Amsterdam, Netherlands
  5	Ramesh	New Delhi	28	3000
  6	Huang	China	28	2800	Yizhuangzhen, Beijing, China
  					Yishun, Singapore
  					Koto City, Tokyo, Japan