easyJoinsSQL
Employees Earning More Than Their Manager
Use a self-join to find employees who are paid more than their own manager: the standard SQL interview question on joining a table to itself.
SQL
The table employees(id, name, salary, manager_id, dept_id) holds every employee. manager_id is the id of the employee's manager, or NULL for someone with no manager.
Write a query that returns the name of every employee whose salary is strictly greater than their manager's salary. Return one column, name, in any order.
Examples
Example 1
Input: employees (id, name, salary, manager_id): (1, Mira, 9000, NULL) (2, Ravi, 9500, 1) (3, Tara, 7000, 1) Output: name Ravi Why: Ravi earns 9500 and his manager Mira earns 9000. Tara earns less than Mira, and Mira has no manager.
Constraints
- manager_id is NULL for employees without a manager.
- Equal salaries do not count: the employee must earn strictly more.
- dept_id can be ignored for this problem.
Hints
Hint 1
You need each employee and their manager on the same row.
Hint 2
Join employees to itself with two aliases, e.g. e for the employee and m for the manager.
Hint 3
An INNER JOIN on e.manager_id = m.id naturally drops employees without a manager.
Solution
Show the solution (try it yourself first)
Approach: self-join
Treat the table as two copies: e is the employee and m is the manager. Joining on e.manager_id = m.id puts each employee next to their manager, and the WHERE clause keeps the rows where the employee earns more.
SELECT e.name
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;The inner join drops top-level employees (their manager_id is NULL, which matches nothing), which is what we want: they have no manager to out-earn. A correlated subquery, WHERE salary > (SELECT salary FROM employees WHERE id = e.manager_id), gives the same answer.
Complexity
The join uses the primary key on id, so it is one pass over employees with an index lookup each: O(n log n), effectively linear.
Found a mistake in this problem, its tests or the solution?