181. Employees Earning More Than Their Managers

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.

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.

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.

Solution:

# Write your MySQL query statement below
Select a.Name as Employee 
from Employee As a 
JOIN Employee As b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
;

# Write your MySQL query statement below
Select a.Name as Employee #This line selects the Name column from the first instance of the Employee table (aliased as a) and renames it as Employee in the result.
from Employee As a # This part specifies that we're selecting from the Employee table, and it's aliased as a. Aliasing allows us to reference the table multiple times in the query, which is necessary for the self-join.
JOIN Employee As b # Here, we are performing a self-join on the Employee table by creating a second instance of it, aliased as b. This will allow us to compare employees with their managers.
ON a.ManagerId = b.Id # This line specifies the condition for the join. It matches rows from the first instance (a) to rows in the second instance (b) where the ManagerId of an employee (a.ManagerId) is equal to the Id of the manager (b.Id). Essentially, this links each employee to their respective manager.
AND a.Salary > b.Salary # This condition filters the results to include only those rows where the employee's salary (a.Salary) is greater than their manager's salary (b.Salary). This ensures that the query only returns employees who earn more than their managers.
;