T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

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.

T-SQL FULL OUTER JOIN

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

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto City, Tokyo, JapanM

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

    NameAgeAddress
    John25
    Marry24Brooklyn, New York, USA
    Jo27Grenelle, Paris, France
    Kim30Geuzenveld, Amsterdam, Netherlands
    Ramesh28
    Huang28Yizhuangzhen, 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:

    EmpIDNameCityAgeSalaryAddress
    1JohnLondon253000
    2MarryNew York242750Brooklyn, New York, USA
    3JoParis272800Grenelle, Paris, France
    4KimAmsterdam303100Geuzenveld, Amsterdam, Netherlands
    5RameshNew Delhi283000
    6HuangChina282800Yizhuangzhen, Beijing, China
    Yishun, Singapore
    Koto City, Tokyo, Japan