SQL implementation of LeetCode (181. Employees earn more than managers)

[LeetCode] 181.Employees Earning More Than Their Managers

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
|Employee |
+----------+
| Joe |
+----------+

This question gives us an Employee table, which contains the salary information of employees and their managers. Managers are also employees, and their manager ID is empty. Let's find out the employees whose salary is higher than their managers. Then it is a very simple comparison problem. We can generate two instance objects to interpolate through ManagerId and Id, and then restrict the condition that one Salary is greater than the other:

Solution 1:

SELECT e1.Name FROM Employee e1
JOIN Employee e2 ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary;

We can also skip Join and directly write all the conditions into where:

Solution 2:

SELECT e1.Name FROM Employee e1, Employee e2
WHERE e1.ManagerId = e2.Id AND e1.Salary > e2.Salary;

References:

https://leetcode.com/discuss/88189/two-straightforward-way-using-where-and-join

This is the end of this article about SQL implementation of LeetCode (181. Employees earn more than managers). For more relevant SQL implementation of employees earning more than managers, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!