Write a query to find employees who earn more than their direct manager, using a self-join on an Employees table.

Answer

Self-join the Employees table to its own manager_id, then compare each employee's salary to their manager's.

Solution

SELECT e.employee_id, e.name AS employee_name, e.salary AS employee_salary,
       m.name AS manager_name, m.salary AS manager_salary
FROM Employees e
JOIN Employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Step by Step

1. The table is joined to itself: alias e represents the employee, alias m represents that employee's manager.
2. e.manager_id = m.employee_id is what links each employee row to their manager's row within the same table.
3. WHERE e.salary > m.salary keeps only pairs where the employee out-earns their own manager.

Alternative Method: Correlated Subquery

SELECT e.employee_id, e.name, e.salary
FROM Employees e
WHERE e.salary > (SELECT m.salary FROM Employees m WHERE m.employee_id = e.manager_id);

This avoids a full self-join and can read more simply when you only need the employee's own row, not the manager's details alongside it - though it re-runs the subquery once per employee row rather than joining in one pass.

Key Exam Traps

• Top-level executives have no manager: A row where manager_id is NULL (like the CEO) is automatically excluded by an inner join, since there's no matching manager row - this is actually correct behavior here, but interviewers often ask you to explain why that row disappears.
• Self-join alias clarity: Mixing up which alias (e vs m) represents the employee versus the manager is the single most common mistake in self-join questions - always write out plainly which alias means what before writing the join condition.
• Salary ties: If an employee earns exactly the same as their manager, > correctly excludes them - confirm whether "earn more" should be strict or inclusive with the interviewer.