Add a new column with manager names for each employee using a self-join.

To add a new column with manager names for each employee, you perform a self-join on the employees table, linking each employee's manager_id to another instance of the table representing their manager's employee_id.

This technique involves joining a table to itself, treating it as two separate logical entities. We use distinct aliases, typically e for the employee and m for their manager, to clearly distinguish between the two instances of the employees table. The join condition e.manager_id = m.employee_id correctly matches an employee's foreign key manager_id to the primary key employee_id of their manager. A LEFT JOIN is crucial here because it ensures that all employees are included in the result set, even those who do not have a manager (e.g., the top-level CEO). For such employees, the manager_name column will display NULL, which accurately reflects their lack of a direct supervisor.

SELECT
    e.employee_id,
    e.name AS employee_name,
    m.name AS manager_name
FROM
    employees e
LEFT JOIN
    employees m ON e.manager_id = m.employee_id;

For large employees tables, it's critical to ensure that employee_id (as the primary key) and manager_id (as a foreign key) columns are appropriately indexed. This optimizes join performance by allowing the database engine to quickly locate matching rows, preventing costly full table scans. This self-join pattern is a fundamental technique for querying hierarchical data structures stored within a single relational table, applicable beyond just employee hierarchies.

In the interview, also mention the importance of LEFT JOIN versus INNER JOIN for this specific problem (handling employees without managers) and the performance implications of indexing on the join keys.