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: