Employees Earning More Than Their Managers
Problem
Write a SQL query to find employees whose salary exceeds their manager's salary.
SQL: SELECT e.name AS Employee FROM Employee e JOIN Employee m ON e.managerId = m.id WHERE e.salary > m.salary;
Employee = [(1, Joe, 70000, 3), (2, Henry, 80000, 4), (3, Sam, 60000, NULL), (4, Max, 90000, NULL)]["Joe"]SELECT e.name AS Employee
FROM Employee e JOIN Employee m ON e.managerId = m.id
WHERE e.salary > m.salary;Explanation
Each employee's manager lives in the same Employee table, pointed to by managerId. The trick is a self-join: we use the table twice, once as the employee and once as their manager.
We alias the table as e (the employee) and m (the manager) and join them with ON e.managerId = m.id. This lines up every employee row next to their manager's row, so both salaries sit on the same combined record.
Then the filter WHERE e.salary > m.salary keeps only employees who out-earn their manager, and we select e.name. Employees with no manager (a NULL managerId) never match the join, so they drop out automatically.
Example: Joe earns 70000 and his manager Sam earns 60000, so Joe is kept. Henry earns 80000 but his manager Max earns 90000, so Henry is dropped. The result is ["Joe"].
Internally the join uses an index or hash on id, making each manager lookup fast.