Display employees and their manger name in SQL

This is one of the most asked SQL interview Question that write a SQL Query to display Employees and Manager name of the employee.

Let’s create a sample table using below scripts.

CREATE TABLE EmployeeDetails
(EmpID INT, EMPName VARCHAR(100), MailID VARCHAR(50), MANAGER_ID INT);

INSERT INTO EmployeeDetails 
(EmpID, EMPName, MailID, MANAGER_ID) VALUES
(101, 'Jhone Henery', 'jh@xyz.com', 0),
(102, 'Mohit Singh', 'ms@xyz.com', 101),
(103, 'Jenlia jr.', 'jjr@xyz.com', 101),
(104, 'Akash Kumar', 'ak@xyz.com', 101),
(105, 'Mangesh Singh', 'ms@xyz.com', 103),
(106, 'Ajay Agarwal', 'aa@xyz.com', 103),
(107, 'Punit Negi', 'pn@xyz.com', 102),
(108, 'Suraj Gupta', 'sg@xyz.com', 105),
(109, 'Ranjit Singh', 'rs@xyz.com', 105),
(110, 'Manmeet kumar Singh', 'mks@xyz.com', 107);

Now, we have created a table name EmployeeDetails and inserted some sample records into this table.

SELECT * FROM EmployeeDetails;

Now to display employees and their managers name in SQL, we need to use self-join on the EmployeeDetails table as shown below.

SELECT emp.EMPID, emp.EMPName, emp.MailID, Mgr.EMPName AS MangerName 
FROM EmployeeDetails emp 
LEFT OUTER JOIN EmployeeDetails mgr
ON emp.manager_id = mgr.empid

Let’s understand the above SQL Query, it uses the left outer join to match each employees with their corresponding manager based on the Manager_ID in the EmployeeDetails table and returns null for non matching Manager_ID.

Let’s execute the query and you can see the output. It returns the employees and their managers name.

 

Also Read..

SQL Interview Questions

How to compare column names in two tables in SQL Server ?

How to Insert same row multiple times in a table in SQL Server?

How to get the size of all tables in SQL Server?

How to get top Nth highest salary in each department?

Calculate Rolling average?

How to get multiple row data in single row in SQL?

How to reset identity column value in table?

 

 239 total views,  5 views today