SELF JOIN in SQL

Definition :  A self-join is simply a normal SQL join that joins one table to itself.

Scenario : Consider an employee Table which holds information of all employees of the company. Each employee record in the table has a manager shown in the column ManagerId, who is also an employee for the same company. This way, all the employees and their respective managers are present in the same table.

USE XYZCompanyDB
GO

-- Create an Employee Table
CREATE TABLE EmployeeInfo
(
   EmployeeID INT PRIMARY KEY,
   EmployeeName NVARCHAR(50),
   ManagerID INT
)
GO

-- Insert some Sample Data
INSERT INTO EmployeeInfo VALUES(1, 'Mike', 3)
INSERT INTO EmployeeInfo VALUES(2, 'David', 3)
INSERT INTO EmployeeInfo VALUES(3, 'Roger', NULL)
INSERT INTO EmployeeInfo VALUES(4, 'Marry',2)
INSERT INTO EmployeeInfo VALUES(5, 'Joseph',2)
INSERT INTO EmployeeInfo VALUES(7, 'Ben',2)
GO

-- Check the data
SELECT * FROM EmployeeInfo
GO

Problem : Find all employee names and their manager names in the company.
That is, in this scenarion, How would you create a query that will return the names of the manager of each employee?

Solution: We will now use inner join to find the employees and their managers' details.

-- Use Inner Join
SELECT e1.EmployeeName As EmployeeName, e2.EmployeeName AS ManagerName
FROM EmployeeInfo e1
INNER JOIN EmployeeInfo e2
ON e1.ManagerID = e2.EmployeeID
GO


And the result is :

From the result set, we can see that all the employees who have a manager are visible.