What is SELF JOIN ? with Realtime Example

Welcome to Appsloveworld Technologies, In this session, we’ll talk about self join, which is nothing but joining a table with itself, will also talk about self joined classification and Purpose of Self-Join.

Most of the people think self-join is a different type of join altogether, which is not true. Self-join can be classified as inner, outer, cross join depending on how you write the self join.

Before continuing with the session. I strongly recommend reading SQL join tutorial first. In this post, we have already discussed how to join different tables.

• Sql joins with realtime examples

What is Self Join?

If you look at the table here that we have, it has got three columns, EmpId, EmployeeName, and Manager_Id.

Now let’s say I want you to write a query which gives me an output like this. I want the employee’s name as well as his manager’s name.

If you need SQL script to create these two tables, you can find the script at end of the post.

Now if you look at the table on the left-hand side, if you look at Michael, Michael’s manager ID is 5 and you take this 5 and look up in the employee ID column, 5 is Grayson.

That’s means Michael’s manager is Grayson and along the same lines, if you look at Jackson, his manager ID 6 and you take this 6 and look up in the employee ID column 6 is Gabriel.

so Jackson’s manager is Gabriel. So if you look at the situation here, we are using the Manager_Id column and looking up in the employee ID column within the same table, which means we are referencing the same table.

You can say that i want a sql query to get the employee name and their manager name from the same table.

If we want this kind of output like employee name and manager’s name, there is a need to join this employee table with itself.

Now you might be wondering how can we join the same table with itself. if you look at below query, we have done exactly the same thing.

How to get the employees with their managers from the same table?

Select Emp.EmployeeName as Employee, Mng.EmployeeName as Manager
from TblEmployeeManager Emp
Left Join TblEmployeeManager Mng
On Emp.Manager_Id = Mng.EmpId

So if you look at the output, we want the employee name and the manager’s name. So select employee name, manager name will come to those in the column list, in the select clause.

Query Explanation:

If you look at the form clause, we are saying TblEmployeeManager and we are giving it a table alias Emp and we are saying, join this table left join, we are doing a left join with TblEmployeeManager again.it can be inner join left join whatever.

So select whatever columns you want from TblEmployeeManager give it an alias emp join with the same table TblEmployeeManager and give it another alias Mng, emp for the employee, mng for the manager.

So you can assume now we have two instances of this table and one for employee and then another table like the manager Table.both of the structures will be identical and you’re basically referencing the same table.

And then what you are doing is in the employee table, take the managerId column in the employee table, take the manager Id column and look up the employee ID in the manager’s table, which is nothing but another copy of the same table. and then from the employee table, which is nothing but emp I want the name which is nothing but the employee name.

So I’m giving it alias as an employee and from the manager’s table, I’m getting the name which is nothing but the manager’s name.So that’s how we get this output.

But if you look at the join here, we are joining with the same table. So this is nothing but a self-join.
So when we execute this query, we get the output as we expect.

Get List all employee’s names and their managers by manager name using an inner join

As we know that inner join, will only give the matching rows between the two tables.When we were using left join, we were getting Anthony’s record as well and we get 9 rows.

but when I change it to an inner join.

Select Emp.EmployeeName as Employee, Mng.EmployeeName as Manager
from TblEmployeeManager Emp
Inner Join TblEmployeeManager Mng
On Emp.Manager_Id = Mng.EmpId

Now, when I execute this one, look at what’s going to happen. I only get 8 rows. what happens to the record. It’s not retrieved anymore. Why? Because if you look at Anthony’s record, the managerId does not match with any of the employee Id.

There is no matching record. And if you remember, the inner join will only give you the matching records between the two tables.

Now, you might be wondering where are two tables here? But remember, in the join, we are joining this table with itself. So we are treating a single table like two tables.So you can call this as an inner self join.

why is it called a self join? because you are joining the same table with itself and you are doing an inner join, inner self join.

So along the same lines, you can do it right outer self join and full outer self join. So a self-join is not a different type of join all together. It can be classified under any type of join,inner-outer,left, right, and cross join as well.

Cross join in SQL example employee manager

Select Emp.EmployeeName as Employee, Mng.EmployeeName as Manager
from TblEmployeeManager Emp
Cross Join TblEmployeeManager Mng

So if you want to convert this into a cross, join the first thing you have to do, get rid of the on the clause, and then convert inner join into the cross join. That’s about it. Since we are joining the same table with itself, we call it, you know, a self join .But since we are using cross join, it’s called Let’s Cross Self Join.
OK, now remember in the TblEmployeeManager, we’ve got 9 rows. And if you remember from the definition of the cross, join when you do across, join between two tables, you’ll get the Cartesian product i.e 9*9=81 rows as shown in image.

Let’s say we want to write a SQL query to display all employees under each manager. Let’s say we need to get all employees starting from ManagerId=7 i.e from the super manager.

;WITH QueryResult (Manager_Id, EmpId, LEVEL)
AS 
(
    SELECT DISTINCT Manager_Id, EmpId AS EmployeeId,  0 AS LEVEL
    FROM TblEmployeeManager  

    UNION ALL

    SELECT tbl.Manager_Id, tbl.EmpId, LEVEL + 1 AS LEVEL
    FROM TblEmployeeManager tbl
      INNER JOIN QueryResult T 
        ON tbl.Manager_Id = T.EmpId
    WHERE tbl.Manager_Id <> tbl.EmpId 
)  
SELECT DISTINCT EmpId, Manager_Id, LEVEL FROM QueryResult ORDER BY Manager_Id

Sql Table Script

--Self join Table
CREATE TABLE [dbo].[TblEmployeeManager](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [nvarchar](max) NULL,
    [Manager_Id] [int] NULL,
 CONSTRAINT [PK_TblEmployeeManager] PRIMARY KEY CLUSTERED 
(
    [EmpId] ASC
)
) 
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] ON 
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (1, 'Michael', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (2, 'Jackson', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (3, 'Jacob', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (4, 'Luke', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (5, 'Grayson', 8)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (6, 'Gabriel', 9)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (7, 'Anthony', NULL)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (8, 'Lincoln', 7)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (9, 'Ryan', 7)
GO

The post What is SELF JOIN ? with Realtime Example appeared first on Software Development | Programming Tutorials.