July 8, 2010 by pinaldave
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.
Trovato qui.
