Skip to content

LeetCode 181 - Employees Earning More Than Their Managers

Database Language: MySQL

Difficulty: ⭐

Problem Description

Input

Table: Employee

Column Name Type
id int
name varchar
salary int
managerId int

id is the primary key (column with unique VALUES) for this table. Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.

Requirement

Write a solution to find the employees who earn more than their managers.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1

Input

Employee table:

id name salary managerId
1 Joe 70000 3
2 Henry 80000 4
3 Sam 60000 NULL
4 Max 90000 NULL
Output
Employee
Joe
Explanation

Joe is the only employee who earns more than his manager.

SQL Schema

CREATE TABLE IF NOT EXISTS Employee (id INT PRIMARY KEY, name VARCHAR(255), salary INT, managerId INT);
TRUNCATE TABLE Employee;
INSERT INTO Employee (id, name, salary, managerId) VALUES ('1', 'Joe', '70000', '3');
INSERT INTO Employee (id, name, salary, managerId) VALUES ('2', 'Henry', '80000', '4');
INSERT INTO Employee (id, name, salary, managerId) VALUES ('3', 'Sam', '60000', NULL);
INSERT INTO Employee (id, name, salary, managerId) VALUES ('4', 'Max', '90000', NULL);

Solution

Given that there's only one table mentioned in the question and that both the employee and the manager are managed in the same table, then the question calls for a SELF JOIN. A SELF JOIN is a type of join where a table is joined with itself. To join a table with itself, an alias to the table needs to be used to differentiate it when the table is being used to identify employees (Employee AS emp) and when the table is being used to identify the manager (Employee AS mgr).

Since we are only interested on employees who has a manager so that their salaries can be compared, an INNER JOIN will be used between the emp table and the mgr table joining on emp.managerId and mgr.id:

FROM Employee AS emp INNER JOIN Employee AS mgr
ON emp.managerId = mgr.id

Since we are interested on employees who earn more than their manager, a condition will be added to the query that addresses this requirement:

emp.salary > mgr.salary

This condition can either be added as part of the INNER JOIN condition or in the WHERE clause:

Part of the INNER JOIN:

FROM Employee AS emp INNER JOIN Employee AS mgr
  ON emp.managerId = mgr.id AND
     emp.salary > mgr.salary

Part of the WHERE clause:

FROM Employee AS emp INNER JOIN Employee AS mgr
  ON emp.managerId = mgr.id
WHERE emp.salary > mgr.salary

Lastly, the requirement wants to return just the name of the employee and return it as the Employee column. Since the name of the column in the Employee table is called name, an alias will be assigned to it to rename it as Employee:

SELECT emp.name AS Employee

Putting this all together yields the following queries:

# Final Solution Query
SELECT emp.name AS Employee
FROM Employee AS emp INNER JOIN Employee AS mgr
  ON emp.managerId = mgr.id AND
     emp.salary > mgr.salary

or

# Alternate Solution Query
SELECT emp.name AS Employee
FROM Employee AS emp INNER JOIN Employee AS mgr
  ON emp.managerId = mgr.id 
WHERE emp.salary > mgr.salary

Using any of these 2 queries will return the same result and both queries generate the same query plan:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE emp ALL 4 100.00 Using where
1 SIMPLE mgr eq_ref PRIMARY PRIMARY 4 emp.managerId 1 33.33 Using where
  • Runtime: 310ms
  • Beats: 97.14% as of July 15, 2024