Self Join is an interesting concept in SQL Server that often sparks discussions among developers. It is commonly classified as an Inner Join, but it can actually be classified under any type of join. In this article, we will explore how Self Join can be implemented as both an Inner Join and an Outer Join.

Let’s start by creating a table for an employee using the AdventureWorks Database:

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

Now, let’s use an Inner Join to find the employees and their managers’ details:

-- Inner Join
SELECT e1.Name AS EmployeeName, e2.Name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID

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 result set. This is because the inner join filters out any result that does not have a manager ID.

Now, let’s convert the Inner Join to an Outer Join and see the result set:

-- Outer Join
SELECT e1.Name AS EmployeeName, ISNULL(e2.Name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID

Once we convert the Inner Join to an Outer Join, we can see the Top Manager as well. This is because the outer join includes all the results from the left table (Employee) and matches them with the corresponding results from the right table (also Employee). If there is no match, the right table columns will contain NULL values.

By exploring this example, we have demonstrated how Self Join can behave as both an Inner Join and an Outer Join. While many developers are familiar with this concept, there are still some who may find it confusing. We hope that this article has provided clarity on the subject.