Employees with Manager Name (Self-Join + LEFT JOIN)
Return every employee with direct-manager names, leaving manager fields NULL for top-level employees.
- Joins
- NULL handling
- Sorting
- Distinct values
Exercise brief
Understand the request
Org design analyst An org-chart feed must include executives who have no manager as well as employees who do.
List every employee with the name of their direct manager. Top-level employees (manager_id IS NULL) must still appear — show NULL for manager_first_name and manager_last_name. Return employee_id, first_name, last_name, manager_id, manager_first_name, manager_last_name — ordered by employee_id.
Return
- Return employee_id, first_name, last_name, manager_id, manager_first_name, manager_last_name in this exact left-to-right order.
Constraints
- Self-join employees through distinct aliases.
- Use LEFT JOIN to retain top-level rows.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)manager_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Self-join: alias the same table twice (`e` for employee, `m` for manager). Column references then disambiguate.
Hint 2
Join condition: e.manager_id = m.employee_id (the employee's manager_id matches some other employee's primary key).
Hint 3
LEFT JOIN keeps employees whose manager_id IS NULL — switch to INNER JOIN and they disappear.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, m.first_name AS manager_first_name, m.last_name AS manager_last_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id ORDER BY e.employee_id;Why this works
Self-joins are one of the most-asked join patterns. The trick is realizing the same physical table can play two roles: 'the employee' and 'the manager'. Aliasing makes both roles addressable in the same query.
Success check
All employees appear once and managerless employees retain NULL manager details.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | first_name | last_name | manager_id | manager_first_name | manager_last_name |
|---|---|---|---|---|---|
| 100 | John | Smith | NULL | NULL | NULL |
| 101 | Alice | Johnson | 100 | John | Smith |
| 102 | Bob | Wilson | 100 | John | Smith |
| 103 | Carol | Davis | NULL | NULL | NULL |
| 104 | David | Brown | NULL | NULL | NULL |
| 105 | Emma | Taylor | NULL | NULL | NULL |
| 106 | Frank | Green | 105 | Emma | Taylor |
| 107 | Grace | White | 100 | John | Smith |
| 108 | Henry | Clark | 103 | Carol | Davis |
| 109 | Ivy | Martinez | 104 | David | Brown |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
Self Joins & Hierarchical Queries
Query organization charts, trees, and parent-child relationships.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
Open the interactive workspace and practice across SQL topics.