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 asks for a list of every employee and their manager. But managers are employees too, in the same table. The answer is a self join, a table joined to itself.
- The employees table. Here's the employees table. Each row is a person, and a manager ID column says who they report to.
The manager column
- Manager ID. Let's look. The employee ID, the full name and the manager ID. Ten employees. The manager ID is just another employee ID. Two means Marcus, from row two of this same table. We want the name, not the number.
SELECT employee_id, full_name, manager_id FROM employees;
Join to itself
- Two short names. Start again. The trick is two short names for one table. E is the employee, m is the manager. Select the employee's name, and the manager's name as manager.
SELECT e.full_name, m.full_name AS manager FROM employees AS e - Match the I Ds. Join employees again, as m. On the manager's employee ID equals the employee's manager ID. Run it. Every employee, with their manager's name next to them.
JOIN employees AS m ON m.employee_id = e.manager_id; - Names, not numbers. These names came from the second copy of the table, the one we called m.
Keep the boss
- Nine, not ten. But there are nine rows, not ten. The head of sales has no manager, so the join found no match and dropped that row.
- LEFT JOIN. Make it a left join. That keeps every employee, even with no manager. Ten rows. Everyone is on the list now.
LEFT JOIN employees AS m - The boss. Grace, the head of sales, is back. The manager is null, because nobody is above the head of sales.
Recap
- The self join rule. So, a self join uses the same table twice, with two short names. Match one copy's manager ID to the other copy's ID, and use left join to keep the people at the top.
