SQL Exercise: Employees including the first name of their manager

SQL JOINS on HR Database: Exercise-8 with Solution

8. From the following table, write a SQL query to find the employees and their managers. Return the first name of the employee and manager.

Sample table: employees

Sample Solution:

-- Selecting specific columns (E.first_name AS "Employee Name", M.first_name AS "Manager") from the 'employees' table, aliased as 'E', and the 'employees' table, aliased as 'M'
SELECT E.first_name AS "Employee Name", M.first_name AS "Manager"

-- Performing an INNER JOIN between the 'employees' table (aliased as 'E') and the 'employees' table (aliased as 'M') based on the condition that 'E.manager_id' is equal to 'M.employee_id'
FROM employees E 

JOIN employees M
  ON E.manager_id = M.employee_id;

Sample Output:

Employee Name	Manager
Neena		Steven
Lex		Steven
Alexander	Lex
Bruce		Alexander
David		Alexander
Valli		Alexander
Diana		Alexander
Nancy		Neena
Daniel		Nancy
John		Nancy
Ismael		Nancy
Jose Manuel	Nancy
Luis		Nancy
Den		Steven
Alexander	Den
Shelli		Den
Sigal		Den
Guy		Den
Karen		Den
Matthew		Steven
Adam		Steven
Payam		Steven
Shanta		Steven
Kevin		Steven
Julia		Matthew
Irene		Matthew
James		Matthew
Steven		Matthew
Laura		Adam
Mozhe		Adam
James		Adam
TJ		Adam
Jason		Payam
Michael		Payam
Ki		Payam
Hazel		Payam
Renske		Shanta
Stephen		Shanta
John		Shanta
Joshua		Shanta
Trenna		Kevin
Curtis		Kevin
Randall		Kevin
Peter		Kevin
John		Steven
Karen		Steven
Alberto		Steven
Gerald		Steven
Eleni		Steven
Peter		John
David		John
Peter		John
Christopher	John
Nanette		John
Oliver		John
Janette		Karen
Patrick		Karen
Allan		Karen
Lindsey		Karen
Louise		Karen
Sarath		Karen
Clara		Alberto
Danielle	Alberto
Mattea		Alberto
David		Alberto
Sundar		Alberto
Amit		Alberto
Lisa		Gerald
Harrison	Gerald
Tayler		Gerald
William		Gerald
Elizabeth	Gerald
Sundita		Gerald
Ellen		Eleni
Alyssa		Eleni
Jonathon	Eleni
Jack		Eleni
Kimberely	Eleni
Charles		Eleni
Winston		Matthew
Jean		Matthew
Martha		Matthew
Girard		Matthew
Nandita		Adam
Alexis		Adam
Julia		Adam
Anthony		Adam
Kelly		Payam
Jennifer	Payam
Timothy		Payam
Randall		Payam
Sarah		Shanta
Britney		Shanta
Samuel		Shanta
Vance		Shanta
Alana		Kevin
Kevin		Kevin
Donald		Kevin
Douglas		Kevin
Jennifer	Neena
Michael		Steven
Pat		Michael
Susan		Neena
Hermann		Neena
Shelley		Neena
William		Shelley

Code Explanation:

The said query in SQL that retrieves the first name of each employee along with the first name of their manager. It uses a join to match each employee with their corresponding manager based on the manager's ID in the employees table.
The resulting output will have two columns: "Employee Name" and "Manager". The "Employee Name" column will contain the first name of each employee, while the "Manager" column will contain the first name of their respective manager.

Visual Presentation:

Alternative Solutions:

Using WHERE Clause with Equality Check:

SELECT E.first_name AS "Employee Name", M.first_name AS "Manager"
FROM employees E, employees M
WHERE E.manager_id = M.employee_id;

Using ANSI-92 JOIN Syntax:

SELECT E.first_name AS "Employee Name", M.first_name AS "Manager"
FROM employees E
JOIN employees M ON E.manager_id = M.employee_id;

Using Subquery with a Self-Join:

SELECT E.first_name AS "Employee Name", 
       (SELECT M.first_name
        FROM employees M
        WHERE E.manager_id = M.employee_id) AS "Manager"
FROM employees E;

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Employees who earn less than employee 182.
Next SQL Exercise: Display the department name, city for each department.