Self Join In SQL Server 2017

Introduction

 
In this article, I am going to explain about self join and types of self joins with examples. Here we will be using SQL Server 2017 or you can use SQL Server 2008 or above.
 
Read my previous article about Joins in SQL Server 2017 using the below links before reading this article,
Definition
 
Joining a table with itself is called a self join. It is not a different type of join. It can be classified under any type of join like INNER Join, OUTER Join, and CROSS Join.
 
Use
 
It is use dfor querying hierarchical data or comparing rows within the same table.
 

Types of self Joins

 
Self join can be classified as,
  • Inner self Join
  • Outer self Join
  • Cross self Join
Again, Outer Self Joins are divided into three types.
  • Left Self Join or Left Outer self Join
  • Right Self Join or Right Outer self Join
  • Full Self Join or Full Outer self Join
Now, I am going to explain the different types of self joins with examples and the differences between them.
 
Prerequisites
 
SQL Server 2017 or you can use SQL server 2008 or above version.
 
Now, first we will create a Database and one table to apply the self joins for understanding.
 

Creating a Database and One Table

 
Step 1 - Create a Database
 
Open your SQL Server and use the following script to create the “chittadb” Database.
 
Create database chittadb
 
Now, select the script query then press F5 or click on Execute button to execute the above script.
 
You should see a message, “Command(s) completed successfully.” This means your new database is created.
 
Step 2 - Create a table
 
Open your SQL Server and use the following script to create a table “tblEmployee”.
  1. Create table tblEmployee  
  2. (  
  3.      EmpID int primary key,  
  4.      Name nvarchar(50),  
  5.      ManagerId int  
  6. )  
Execute the above query to create “tblEmployee “.
 
You should see a message, “Command(s) completed successfully.”
 
Now, data is inserted into the table.
  1. Insert into tblEmployee values ( 1, 'Chitta', 3)  
  2. Insert into tblEmployee values ( 2, 'Chandin', 4)  
  3. Insert into tblEmployee values ( 3, 'Nabin'null)  
  4. Insert into tblEmployee values ( 4, 'Mitu', 1)  
  5. Insert into tblEmployee values ( 5, 'Jitu', 1)  
Execute the above query, you should see a message, “Command(s) completed successfully.”
 
Now retrieve all data from “tblEmployee” table.
  1. select * from tblEmployee  
output
 
Self Join in SQL Server 2017 
 
General Formula for Self Joins
  1. SELECT      ColumnList (whatever column you want to display)   
  2. FROM         Table T1  
  3. JOIN_TYPE Table T2   
  4. ON                 JoinCondition  
T1 and T2 are different table alias names for the same table.
 

Inner Self Join

 
Inner join returns only the matching rows between both the tables, non-matching rows are eliminated.
 
Example
 
Write a query, to retrieve Employee Name and Manager Name from tblEmployee.
 
Inner Self Join tblEmployee table Query
  1. Select E.Name as EmployeeName, M.Name as ManagerName  
  2. from tblEmployee E  
  3. Inner Join tblEmployee M  
  4. On E.ManagerId = M.EmpID  
OutPut
 
Self Join in SQL Server 2017 
 

LEFT SELF JOIN or LEFT OUTER SELF JOIN

 
Left Join or Left Outer Join returns only the matching rows between both the tables, plus non-matching rows from the left table.
 
Example
 
Write a query, to retrieve Employee Name and Manager Name from tblEmployee.
 
LEFT Self JOIN or LEFT OUTER Self JOIN Query
  1. Select E.Name as EmployeeName, M.Name as ManagerName  
  2. from tblEmployee E  
  3. Left Join tblEmployee M  
  4. On E.ManagerId = M.EmpID  
OutPut
 
Self Join in SQL Server 2017 
 

RIGHT SELF JOIN or RIGHT OUTER SELF JOIN

 
Right Join or Right Outer Join returns only the matching rows between both the tables, plus non matching rows from the right table.
 
Example
 
Write a query, to retrieve Employee Name and Manager Name from tblEmployee.
 
RIGHT SELF JOIN or RIGHT OUTER SELF JOIN Query
  1. Select E.Name as EmployeeName, M.Name as ManagerName  
  2. from tblEmployee E  
  3. Right Join tblEmployee M  
  4. On E.ManagerId = M.EmpID  
OutPut
 
Self Join in SQL Server 2017 
 

FULL SELF JOIN or FULL OUTER SELF JOIN

 
Full Join or Full Outer Join returns all rows from both the tables (left & right tables), including non matching rows from both the tables.
 
Example
 
Write a query, to retrieve Employee Name and Manager Name from tblEmployee.
 
FULL SELF JOIN or FULL OUTER SELF JOIN Query
  1. Select E.Name as EmployeeName, M.Name as ManagerName  
  2. from tblEmployee E  
  3. Full Join tblEmployee M  
  4. On E.ManagerId = M.EmpID  
OutPut
 
Self Join in SQL Server 2017 
 

CROSS SELF JOIN


CROSS JOIN, produces the Cartesian product of the 2 tables.
 
For example, in the tblEmployee table we have 5 rows; also in the tblEmployee we have 5 rows. So, a cross joins between these 2 tables produces 25 rows. Cross Join shouldn't have ON clause. 
 
Example
 
Write a query, to retrieve Employee Name and Manager Name from tblEmployee.
 
Note
Cross Join shouldn't have ON clause. 

CROSS Self JOIN Query
  1. Select E.Name as EmployeeName, M.Name as ManagerName  
  2. from tblEmployee E  
  3. cross Join tblEmployee M  
OutPut
 
Self Join in SQL Server 2017 
 

Conclusion

 
In this article, I explained self joins in SQL Server with examples. I hope this article has helped you to understand this topic. Post your valuable feedback in the comments section.