Self Join has always been an note-worthy case. It is interesting to ask questions on self join in a room full of developers. I often ask – if there are three kind of joins, i.e.- Inner Join, Outer Join and Cross Join; what type of join is Self Join? The usual answer is that it is an Inner Join. In fact, it can be classified under any type of join. I have previously written about this in my interview questions and answers series. I have also mentioned this subject when I explained the joins in detail over SQL SERVER – Introduction to JOINs – Basic of JOINs.
When I mention that Self Join can be the outer join, I often get a request for an example for the same. I have created example using AdventureWorks Database of Self Join earlier, but that was meant for inner join as well. Let us create a new example today, where we will see how Self Join can be implemented as an Inner Join as well as Outer Join.
Let us first create the same table for an employee. One of the columns in the same table contains the ID of manger, who is also an employee for the same company. This way, all the employees and their managers are present in the same table. If we want to find the manager of a particular employee, we need use self join.
USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO
We will now use inner join to find the employees and their managers’ details.
-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
From the result set, we can see that all the employees who have a manager are visible. However we are unable to find out the top manager of the company as he is not visible in our resultset. The reason for the same is that due to inner join, his name is filtered out. Inner join does not bring any result which does not have manager id. Let us convert Inner Join to Outer Join and then see the resultset.
-- Outer Join
SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO
Once we convert Inner Join to Outer Join, we can see the Top Manager as well. Here we have seen how Self Join can behave as an inner join as well as an outer join.
As I said earlier, many of you know these details, but there are many who are still confused about this concept. I hope that this concept is clear from this post.
Reference: Pinal Dave (https://blog.sqlauthority.com)
50 Comments. Leave new
Dave
Well explained using the classic example.
Ganesh
Good Clarification.
SELECT e1.Name as EmployeeName, e2.Name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
SELECT e1.Name EmployeeName, ISNULL(e2.Name, ‘Top Manager’) AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
Getting Group By Count In Self Join
SELECT e1.ProductModelID ,count(*) TotalProductModelID,avg(e1.ProductDescriptionID ) as avgProductDescriptionID
FROM Production.ProductModelProductDescriptionCulture e1
inner JOIN Production.ProductModelProductDescriptionCulture e2
ON e1.ProductModelID = e2.ProductModelID
group by e1.ProductModelID
I don’t quite understand this. A ‘self join’ is a concept. Just like a ‘join’ on multiple tables. It is divorced from any notion of the ‘type’ of join. They are separate concerns.
The ‘self’ part refers to how the tables relate and the ‘inner’ or ‘outer’ part refers to the implementation you employ depending on business requirements.
Well said Kam. This example is as much an exercise in realizing the need for an outer join as it is an exercise is the concept of a “self join”.
Separate the two facts:
1: The outer join is required because a record we desire in the result set is being trimmed by using an inner join. When joining two sets of data, an inner join requires the field(s) being joined on to have corresponding values in both sets of data for those records to show in the result set.
We desire to see the complete list of employees, but one employee has a NULL value in the ManagerID field being joined on….so that employee record would be trimmed from the result set using an inner join. Hence, the need for the outer join.
Use set based thinking – don’t get caught up on the fact that the two sets of related data are from the same table.
2: The “self join” concept is expanding the database programmer’s thinking to include the fact that you can join a table to itself, and will need to if the data in the table is hierarchical. (Employee – Manager, Child – Parent, etc…)
Hi All,
How can we join two tables which having no common column. For ref:
Table 1:
ID, Name
Table 2:
Phone, Address
I want to create a view to produce the data as like:
Table:
ID, NAme, Phone, Address
pls help me regarding this.
Sanjay
USE CROSS JOIN ( CARTESIAN JOIN)
CREATE VIEW (ID,NAME,PNO,ADD) AS SELECT A.ID,A.NAME,B.PNO,B.ADD
FROM TABL E A JOIN TABLE B
I HAVENT TRIED IT BUT I FEEL WE CAN USE CROSS JOIN HERE
Incredibly well explained!
i’m looking for a query which can fetch all the employee in-detail where employee id is given as an input and the expected result should be his name, id[team member], his manager name, id[team lead], again his manager name, id[project manager], again his manager name, id[director]. the result should be from top hierarchy or all level of managers.
Hi, i wold like to know how can we retrive Manager who is having more than 2 employee reference . According to the above table it should display
mary – david
joseph – david
ben – david
how can we do this using self join
Please provide me the query if I want retrive all the records who is working under manager 3 and his sub ordinates….
Hi Pinal,
It’s a classic example and helped much to beginners like me.
Thanks & Regards
Pradeep
Bangalore
Great Explanation. Thanks for such a good one :)
good
Hey Sanjay………….
Write query as Select * from tabel1,tabel2
U’ll get the result
how can i change product category and product subcategory and product tables into one parent child table using select statement is there a way to do these
Hi,
I want to know what is the impact of INNER JOIN with OUTER JOIN / OUTER APPLY. Also need to know the impact of adding the inner join inside the outer apply instead of using it outside the outer apply
select few_columns
from table1 a
left outer join table2 b
on b.col1 = a.col1
inner join table3 c
on c.col2 = b.col2
or
select few_columns
from table1 a
outer apply
(select col1, col2, col3
from table2 b
where b.col1 = a.col1
)z
inner join table3 c
on c.col2 = z.col2
or
select few_columns
from table1 a
outer apply
(select col1, col2, col3
from table2 b
inner join table3 c
on c.col2 = b.col2
where b.col1 = a.col1
)z
Please let me know the difference in the performance & the resultset
Thanks nice explanation
dude u rockssssss….nice example…