How to get employee and his Manager's Name from Employee Table

This is frequently asked Interview question to get an Employee Name along with his/her Manager Name in SQL, we have explained this SQL question below using SQL SELF join in SELECT query, so to begin with we have to first create an Employee Table and then write SQL SELECT query to get employee name along with his manager name.

Create Employee Table using following query:

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [EmpName] [nvarchar](200) NOT NULL,
    [EmpDesignation] [nvarchar](200) NOT NULL,
    [EmpDepartment] [nvarchar](200) NOT NULL,
    [ManagerId] [int] NULL
)
GO

Now, insert some Rows into an Employee table using following inserts Script:

INSERT [dbo].[Employee] ([EmpName], [EmpDesignation], [EmpDepartment], [ManagerId]) VALUES ('William Smith', 'Senior Manager', 'Software', NULL);
GO

INSERT [dbo].[Employee] ([EmpName], [EmpDesignation], [EmpDepartment], [ManagerId]) VALUES ('John Parker', 'Senio Developer', 'Software', 1);
GO
INSERT [dbo].[Employee] ([EmpName], [EmpDesignation], [EmpDepartment], [ManagerId]) VALUES ('Ketty Keen', 'Project Lead', 'Software', 1);
GO
INSERT [dbo].[Employee] ([EmpName], [EmpDesignation], [EmpDepartment], [ManagerId]) VALUES ('Ellen White', 'Trainee Software Engineer', 'Software', 3);
GO

Now, we will write SQL SELECT query using LEFT JOIN to the same table to get an Employee Name along with his Manager Name:

SELECT emp.Id as EmpId, emp.EmpName, mgr.Id as ManagerId, mgr.EmpName AS ManagerName
FROM Employee emp
LEFT JOIN Employee mgr
on mgr.Id = emp.ManagerId

In the above result, we can see, it has returned an employee whose Manager is empty, so LEFT JOIN works here to return an employees and their managers even if Manager is empty.

If we don't want to return an employee with empty Manager, then we can replace LEFT JOIN with INNER JOIN in the above query, also we can write query as shown below which works like INNER JOIN with self table to return Employees and their Manages:

SELECT emp.Id as EmpId, emp.EmpName, mgr.Id as ManagerId, mgr.EmpName AS ManagerName
FROM Employee emp, Employee mgr
WHERE mgr.Id = emp.ManagerId 

We hope that you have understood this example to get Employees and their respective Manager Id and Manager Names in SQL.