Find Employee Manager Details Using Self Join
Display each employee along with their manager's name using a self-join. Top-level employees (manager_id IS NULL) must still appear with manager_name = NULL. Return employee_id, employee_name, manager_id, manager_name — ordered by employee_id.
- Joins
- NULL handling
- Sorting
Exercise brief
Understand the request
People operations analyst The employee directory must preserve executives who have no manager while resolving every valid reporting relationship.
Return
- Return the four requested employee and manager columns.
- Order by employee_id.
Constraints
- Use two aliases of employees.
- Use LEFT JOIN so root employees remain visible.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERemployee_nameTEXTmanager_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` plays the role of "employee", `m` plays "manager".
Hint 2
Join predicate: e.manager_id = m.employee_id — the employee's manager_id matches some other employee's primary key.
Hint 3
LEFT JOIN keeps the CEO (manager_id IS NULL); INNER JOIN would drop them.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT e.employee_id, e.employee_name, e.manager_id, m.employee_name AS manager_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 the foundational hierarchy pattern. The same physical table plays two roles (employee + manager); aliases make both addressable. Always LEFT JOIN unless you specifically want to drop top-level rows.
Success check
All 15 employees appear exactly once and the CEO has a NULL manager name.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | employee_name | manager_id | manager_name |
|---|---|---|---|
| 1 | Alice CEO | NULL | NULL |
| 2 | Bob VP Sales | 1 | Alice CEO |
| 3 | Carol VP Eng | 1 | Alice CEO |
| 4 | David VP HR | 1 | Alice CEO |
| 5 | Emma Sales Mgr | 2 | Bob VP Sales |
| 6 | Frank Eng Mgr | 3 | Carol VP Eng |
| 7 | Grace HR Mgr | 4 | David VP HR |
| 8 | Henry Sales Rep | 5 | Emma Sales Mgr |
| 9 | Ivy Sales Rep | 5 | Emma Sales Mgr |
| 10 | Jack Engineer | 6 | Frank Eng Mgr |
Previewing 10 of 15 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Joins
Practice reliable INNER, LEFT, FULL, CROSS, self, semi, anti, range, temporal, and many-to-many join patterns.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
Open the interactive workspace and practice across SQL topics.