Beginner

527. Self JOIN for Employee Hierarchy

Use a self JOIN to display employees and the managers they report to.

📘 Lesson 527 of 728 ⏱ 13 min 🎯 Intermediate

An employee hierarchy can be represented in one Employees table where each employee has a ManagerID referencing another employee's EmployeeID. A self JOIN connects employees with their managers.

Organizational structures are commonly stored in a single table and need hierarchical queries for reporting.

Display every employee along with the name of their direct manager.

SQL 
SELECT
    e.EmployeeID,
    e.EmployeeName,
    m.EmployeeName AS ManagerName
FROM Employees e
LEFT JOIN Employees m
    ON e.ManagerID = m.EmployeeID;

Used in HR reporting, organizational charts, approval workflows, and management dashboards.

  • ManagerID can reference EmployeeID.
  • The same table can represent employees and managers.
  • Aliases distinguish the two roles.
  • LEFT JOIN preserves top-level employees.
  • Self JOINs support hierarchy reporting.
How can employee-manager data be queried?

Use a self JOIN where ManagerID from one employee row matches EmployeeID of another employee row.

Why use LEFT JOIN for employee hierarchy?

It preserves employees who do not have a manager, such as the top-level employee.

Have you finished this lesson?

Introduction to SQL – All Lessons