What is Self Join in SQL?|Self Join in SQL with multiple examples

In my previous article i have given different SQL Joins Interview Questions and answers with real life examples. In this article i would like to give you Self Join in SQL with multiple real life examples.In our day to day industry examples we are using self joins.The self join is not only used for adding business logic but also for reporting purpose in real life examples.The Self Join is nothing but the Table is joining with itself to achieve the specific purpose.

Self Join in SQL is table which is joined with itself

Self Join Syntax and examples :

In this section i would like to give you Self Join Syntax with real life industry examples.This join allows to join table to itself with using the concept of aliases,This Join is useful in two different scenarios:

1.Self Join is used to Fetch the Hierarchical data

2.Self Join is used to compare values in same table.

Syntax :

Select Column_name1,Column_name2….Column_name ‘N’ From

Table_Name alias,Table_Name alias where Condition of Self Join;

In Syntax the Table_Name is same table name.

User can not only used the this join to connect table with inner join but also with left outer join as well.

I would like to explain you different examples,

Example 1 : Fetching Hierarchical data

The self join is used to fetch the Hierarchical data.But make sure that the table has that structure.

Lets consider the following example,

Table_name : Employee

Employee ID	Employee Name	Managner Id	Salary
1	Amit	6	50000
2	Romi	1	15000
3	Deepti	1	15000
4	Rahul	6	40000
5	Pintoo	1	15000
6	Shweta		75000

Question : I would like to fetch the Employee_Name with its Manager_Name. If the Employee has no Manager then it will show output as no Manager.

I would like to use the modular way of query writing,

Step 1 : Check for Employee and Manager Data

Select Employee_Name,Employee_Id,Manager_Id from Employee;

Step 2 : Use of Aliases and join tables

Select a.Employee_Name,b.Employee_Name ‘Manager_Name’ from Employee a , Employee b where a.Employee_Id=b.Employee_Id;

Step 3: Use of Left Outer join and case statement to fetch Manager name and if Manager is not there show that ‘No Manager’

Select a.Employee_Name,Case when b.Manager_Id is not null then b.Employee_Name else ‘No Manager’ As ‘Manager Name’ from Employee a,Employee b where a.Employee_Id=b.Manager_id(+);

Answer :

Select a.Employee_Name,Case when b.Manager_Id is not null then b.Employee_Name else ‘No Manager’ As ‘Manager Name’ from Employee a,Employee b where a.Employee_Id=b.Manager_id(+);

Output :

Employee Name	Manager Name
Amit	Shweta
Romi	Amit
Deepti	Amit
Rahul	Shweta
Pintoo	Amit
Shweta	No Manager

Using this we have fetched the Hierarchical data. We need Hierarchical data sometimes in creating the reports.

Example 2 : Comparing Values in Database

The self join is also used to compare the values in database. If User wants to find the data like Manager_Name with its reportee name then also user needs to use join,

Step 1 : Check for Manager Data

Select Employee_Name,Manager_Id from Employee;

Step 2 : Finding Manager_Name and Employee_Name as Reporter of that manager.

Select a.Employee_Name as ‘Manager_Name’,b.Employee_Name as ‘Reporter’ From Employee a, Employee b where a.manager_id=b.employee_id(+);

Output :

Manager_Name	Reporter
Shweta	Amit
Amit	Romi
Amit	Deepti
Shweta	Rahul
Amit	Pintoo

These are different examples of Self join with real life industry examples. I am hoping that you will get exact idea about it. If you like this article or if you have any issues with the same kindly comment in comments section.