Problem
Task
Return each employee name with the name of their manager, keeping employees who have no manager.
Schema
Table Schema
employees(id, name, manager_id)Input
Sample Data
| id | name | manager_id |
|---|---|---|
| 1 | Mina | NULL |
| 2 | Daniel | 1 |
| 3 | Sofia | 1 |
Output
Expected Output
| employee_name | manager_name |
|---|---|
| Mina | NULL |
| Daniel | Mina |
| Sofia | Mina |
Answer
Check Your Solution
Show Answer and Explanation
Correct Answer
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.id
ORDER BY e.id;Explanation
A self join uses the same table twice with different aliases. LEFT JOIN keeps employees whose manager_id is NULL.
Common Mistakes
- Using the same alias for both copies of employees.
- Joining e.id = m.manager_id, which reverses the relationship.
- Using INNER JOIN and dropping employees without managers.
Concepts
Related Concepts
Self Join
LEFT JOIN
Aliases
Next practice