SQL Exercise: Display employees and their manager

SQL JOINS on HR Database: Exercise-11 with Solution

11. From the following table, write a SQL query to find the employees and their managers. Those managers do not work under any manager also appear in the list. 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 a LEFT OUTER 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 

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

Sample Output:

Employee Name	Manager
Steven	
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 (if they have one). It uses a left outer join to match each employee with their corresponding manager based on the manager's ID in the employees table.
If an employee does not have a manager, the "Manager" column will contain NULL for that row.
The left outer join is used in case an employee does not have a manager associated with them, so that the query will still return information about that employee.

Visual Presentation:

Alternative Solutions:

Using Subquery with 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;

Explanation:

This query uses a subquery to retrieve the manager's first name for each employee. It correlates the subquery with the main query using 'manager_id' and 'employee_id'.

Using INNER JOIN and Aliasing:

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;

Explanation:

This query uses an INNER JOIN to link employees and managers based on matching 'manager_id' and 'employee_id'. It retrieves the first names of both employees and managers.

Using Subquery with EXISTS Clause:

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

Explanation:

This query employs a subquery with an EXISTS clause to find the manager's first name for each employee. The subquery checks if there exists an employee whose 'employee_id' matches the manager's 'manager_id'.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Display employees who have or have not any department.
Next SQL Exercise: Employees who work in the same department as Taylor.