Employees Earning More Than Their Manager — SQL Easy Problem

Problem statement

Every employee except the people at the top reports to a manager, who is also a row of the Employee table.

Return the name of every employee whose salary is strictly greater than their manager's salary, in a column named employee. Employees without a manager are never in the answer. Return the rows in any order.

Tables

Table: Employee

Primary key: id.

managerId is the id of the employee's manager, or NULL for someone with no manager.

Examples

Example 1

Employee

Output

How to solve Employees Earning More Than Their Manager

The comparison is between two rows of one table: an employee and the row of their manager. A self join puts those two rows side by side — the table appears twice in FROM under two aliases, e for the employee and m for the manager, and the join condition e.managerId = m.id pairs each employee with exactly one manager row.

Once the rows are paired, the question is a plain filter: keep the pairs where e.salary > m.salary, and select the employee's name under the alias the statement asks for.

People with no manager have managerId NULL. NULL = m.id is never true, so an inner join drops them without any extra condition — which is what the statement wants. A correlated subquery that looks up the manager's salary works too: for a NULL managerId the subquery returns no row, the comparison is NULL, and the row is filtered out the same way.

Equal salaries are not "more", so the comparison is strict. The join reads each employee once and finds the manager through the primary key, so it is linear in the size of the table with an index on id.

Reference solution (MySQL)

SELECT e.name AS employee
FROM Employee e
JOIN Employee m ON e.managerId = m.id
WHERE e.salary > m.salary

Another way

SELECT name AS employee FROM Employee e WHERE salary > (SELECT salary FROM Employee m WHERE m.id = e.managerId)

Another way

SELECT e.name AS employee FROM Employee e, Employee m WHERE e.managerId = m.id AND e.salary > m.salary