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.

How To Join 3 Tables in SQL

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.

8 Replies to “What is Self Join in SQL?|Self Join in SQL with multiple examples”

  1. Very good. Is this blog enough to prepare for SQL interviews , for complex queries also?

    Appreciate the response.

    Thank you

  2. Good effort but Felt there are many mistakes….please execute before publishing and see.

    1. Hello Soumya,

      Kindly let me know what are different mistakes… I will correct that. I am always running queries and then write my articles.

      Regards,
      Amit S

Comments are closed.