7. Find Employees Earning More Than Their Manager

The following problem comes from LeetCode.

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

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

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

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

Define the Problem

The problem is a bit tricky, but it's been defined fairly well.

---

Think

Jonathan thinks about the problem for a bit, particularly about the Sequel syntax. Here's his pseudocode:

/* Retrieve all employees from Employee table where Manager matches the employee id and salary
is greater than manager.*/

To Jonathan, this problem seems pretty straightforward, so he moves on to writing out the syntax.

---

Write Code and Syntax

Now that the hard part is done, Jonathan just needs to write out the syntax to fill in the pseudocode. As Jonathan writes the code, he explains the syntax.

/* Retrieve all employees from Employee table where Manager exists and salary is greater than manager.
SELECT
    *
FROM
    Employee AS a,
    Employee AS b
WHERE
    a.ManagerId = b.Id
        AND a.Salary > b.Salary
;

Jonathan: I decided to use aliases to make things easier in the syntax, gathering all columns where the manager Id of the employee matches the Id of the employee in the Employee table. I also further clarified by having an AND statement showing that Employee a's salary must be greater than Employee b's salary.