[LeetCode] MySQL 181. Employees Earning More Than Their Managers (SELF JOIN 예제)

Employees Earning More Than Their Managers - LeetCode

Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.

Write an SQL query to find the employees who earn more than their managers.

Return the result table in any order.

The query 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.

• 자신의 매니저(managerID)보다 돈을 더 많이 버는 직원의 name을 조회하는 문제이다.
  • SELF JOIN을 이용해서 Manager라는 이름의 새로운 Employee 테이블을 하나 더 병합한다고 생각한다.
    • Manager 테이블에서 매니저의 id와 Employee에게 배정된 매니저 id가 같은 사람의 월급을 서로 비교해서 매니저보다 더 월급이 많을 경우의 결과를 뽑아내도록 한다.

 

[MySQL]

# Write your MySQL query statement below
SELECT Employee.name AS Employee
FROM Employee
    INNER JOIN Employee AS Manager ON Employee.managerid = Manager.id 
WHERE Employee.salary > Manager.salary