Employees and Their Managers (Self Join) | DrillTheStack

Step 8 of 25 · SQL Interview Preparation for Beginners · Stage 3: Aggregates and joins

Employees and Their Managers (Self Join)

sqlSQLbeginnerinterview-prep

Join a table to itself to list each employee next to their manager, keeping the top-level employee who has no manager.

-- Every employee with their manager's name.
-- A self join uses the same table twice, under two aliases: e (the employee) and m (the manager).
-- LEFT JOIN keeps the top-level employee, whose manager_id is NULL.
SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;