LeetCode 181 - Employees Earning More Than Their Managers
Database Language: SQL Server
Difficulty: Easy
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 [dbo].[Employee] (id INT PRIMARY KEY, name VARCHAR(255), salary INT, managerId INT);
TRUNCATE TABLE [dbo].[Employee];
INSERT INTO [dbo].[Employee] (id, name, salary, managerId) VALUES ('1', 'Joe', '70000', '3');
INSERT INTO [dbo].[Employee] (id, name, salary, managerId) VALUES ('2', 'Henry', '80000', '4');
INSERT INTO [dbo].[Employee] (id, name, salary, managerId) VALUES ('3', 'Sam', '60000', NULL);
INSERT INTO [dbo].[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.
|--Nested Loops(Inner Join, OUTER REFERENCES:([emp].[salary], [emp].[managerId]))
|--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Employee].[PK_Employee] AS [emp]))
|--Clustered Index Seek(OBJECT:([leetcode].[dbo].[Employee].[PK_Employee] AS [mgr]),
SEEK:([mgr].[id]=[leetcode].[dbo].[Employee].[managerId] as [emp].[managerId]),
WHERE:([leetcode].[dbo].[Employee].[salary] as [emp].[salary]>[leetcode].[dbo].[Employee].[salary] as [mgr].[salary])
ORDERED FORWARD)
Related Articles: