Self-Joins for Organizational Hierarchy in SQL

Question

Given an `employees` table (emp_id, emp_name, manager_id), write a query to list each employee along with their direct manager's name. If an employee has no manager (e.g. CEO), display 'Top Executive'.

Accepted Answer

Optimal Solution:
SELECT 
  e.emp_id,
  e.emp_name AS employee_name,
  COALESCE(m.emp_name, 'Top Executive') AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id
ORDER BY e.emp_id ASC;

Technical Explanation:
A self-join pairs the `employees` table alias `e` (as employee) with alias `m` (as manager). We use a `LEFT JOIN` so top-level leaders without managers are preserved, and `COALESCE` replaces NULL with 'Top Executive'.

emp_id	employee_name	manager_name
1	Rajesh Mehta	Top Executive
2	Kaushal Kumar	Rajesh Mehta
3	Pooja Sharma	Rajesh Mehta
4	Ankit Verma	Kaushal Kumar