Solving tips
- A self join lists the same table twice with different aliases so each row can reference another row in the table.
- Match the child's manager_id to the parent's id; use a LEFT JOIN so the top boss (manager_id NULL) is not dropped.
- Alias every column you output to avoid ambiguity, since both aliases expose a name column.
Each employee reports to another employee via manager_id. Show every employee next to the name of their manager.
Schema
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
manager_id INT REFERENCES employees(id)
);
employees
| id | name | manager_id |
|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
| 4 | Dave | 2 |
| 5 | Eve | 2 |
Task
Return one row per employee with columns employee_name and manager_name. Include employees who have no manager, showing manager_name as NULL for them. Order by employee_name ascending.
Expected output
| employee_name | manager_name |
|---|
| Alice | NULL |
| Bob | Alice |
| Carol | Alice |
| Dave | Bob |
| Eve | Bob |
Approach
Reference the employees table twice with two aliases: e for the employee and m for the manager. Joining e.manager_id = m.id lines each employee up with the row describing their boss. Use a LEFT JOIN so an employee whose manager_id is NULL still appears, with the manager columns coming back NULL.
Query
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.name;
Walkthrough
- Alice has
manager_id = NULL, so the LEFT JOIN finds no manager row and manager_name is NULL, but she is still returned.
- Bob and Carol both have
manager_id = 1, which matches Alice’s id, so their manager is Alice.
- Dave and Eve both have
manager_id = 2, which matches Bob’s id, so their manager is Bob.
ORDER BY e.name yields Alice, Bob, Carol, Dave, Eve.
Complexity & notes
- The join uses the primary key on the
m side and benefits from an index on manager_id for the probe; the optimizer treats the two aliases as independent scans of the same physical table.
- Pitfall: using an INNER JOIN here silently drops Alice, the top of the hierarchy. Whenever the root of a self-referential tree has a NULL parent, prefer LEFT JOIN unless you explicitly want to exclude it.
- Always qualify columns with the alias;
SELECT name would be ambiguous because both e and m expose name.