SQL Self Join: Employees and Their Managers
The employees table stores each person's manager as another employee's ID. Join the table to itself with two short names to show every employee next to their manager, and use LEFT JOIN to keep the boss at the top.
Step by step
Self join
- Who reports to whom?. HR wants a list of every employee and their manager. Managers are employees too, and they sit in the same table. So the answer is a self join: a table joined to itself.
- The employees table. The employees table has one row per person. A manager_id column says who each person reports to.
The manager column
- Manager ID. First, look at the raw data with SELECT employee_id, full_name, manager_id FROM employees; It returns 10 employees. Each manager_id is just another employee_id. A 2 means Marcus, employee 2 in this same table. We want his name, not the number.
SELECT employee_id, full_name, manager_id FROM employees;
Join to itself
- Two short names. Start a new query. Give the table two short names: e for the employee and m for the manager. Select e.full_name, and m.full_name AS manager, FROM employees AS e.
SELECT e.full_name, m.full_name AS manager FROM employees AS e - Match the I Ds. Add the same table a second time: JOIN employees AS m ON m.employee_id = e.manager_id. This matches each employee's manager_id to the manager's own employee_id. Run it. Each employee now has their manager's name next to them.
JOIN employees AS m ON m.employee_id = e.manager_id; - Names, not numbers. The names in the manager column come from the second copy of the table, the one called m.
Keep the boss
- Nine, not ten. There are 9 rows, not 10. Grace, the head of sales, has no manager, so her manager_id is NULL. A plain JOIN finds no match for her and drops her row.
- LEFT JOIN. Change JOIN to LEFT JOIN employees AS m. A LEFT JOIN keeps every row from e, even when there is no matching manager. Run it again and you get 10 rows. Everyone is on the list.
LEFT JOIN employees AS m - The boss. Grace is back, with NULL in the manager column, because nobody is above the head of sales.
Recap
- The self join rule. A self join uses the same table twice, with two aliases. Match one copy's manager_id to the other copy's employee_id, and use LEFT JOIN so the people at the top stay in the result.
Common mistakes
- Using a plain JOIN. A plain JOIN drops anyone whose manager_id is NULL, so Grace disappears and you get 9 rows instead of 10. Use LEFT JOIN employees AS m to keep everyone.
- Matching the IDs the wrong way round. ON e.employee_id = m.manager_id swaps the roles, so m now holds each person's direct reports, not their manager. Keep it as m.employee_id = e.manager_id: the manager's ID equals the employee's manager_id.
- Leaving out the aliases or the prefixes. With the same table listed twice, the database can't tell the copies apart, and a bare full_name is ambiguous. Give each copy its own alias and prefix every column, like e.full_name and m.full_name.
- Filtering on the manager in WHERE. A condition like WHERE m.full_name <> 'Marcus' also removes the rows where m is NULL, so the LEFT JOIN acts like a plain JOIN again. To leave out Marcus's reports but keep people with no manager, write WHERE m.full_name IS NULL OR m.full_name <> 'Marcus'.
When a business analyst uses this
When you build an approval or sign-off matrix, the HR extract often has only a manager_id for each stakeholder. A self join with LEFT JOIN shows each person's line manager by name. The rows with a NULL manager show where each approval chain ends.
Questions people ask
What is a self join in SQL?
A self join is a normal join where both sides are the same table, told apart by two aliases. You use it when rows point to other rows in the same table, like an employee's manager_id pointing to another employee.
What is the syntax for a self join in SQL?
There is no SELF JOIN keyword. You write an ordinary JOIN or LEFT JOIN and list the same table twice with different aliases, for example FROM employees AS e LEFT JOIN employees AS m ON m.employee_id = e.manager_id (in Oracle, leave out the word AS before the table aliases).
When is a self join used in SQL?
Use it when a table links its own rows together: employees and their managers, parent and child categories, or tasks that depend on other tasks in the same table.
