SQL Exercise: Display employees and their manager
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.