SQL | LeetCode 1978. 조건에 맞는 직원 찾기 (NOT IN)

Table: Employees

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| manager_id  | int      |
| salary      | int      |
+-------------+----------+
In SQL, employee_id is the primary key for this table.
This table contains information about the employees, their salary, and the ID of their manager. Some employees do not have a manager (manager_id is null). 

 

Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_id set to the manager that left.

Return the result table ordered by employee_id.

The result format is in the following example.

 

Example 1:

Input:  
Employees table:
+-------------+-----------+------------+--------+
| employee_id | name      | manager_id | salary |
+-------------+-----------+------------+--------+
| 3           | Mila      | 9          | 60301  |
| 12          | Antonella | null       | 31000  |
| 13          | Emery     | null       | 67084  |
| 1           | Kalel     | 11         | 21241  |
| 9           | Mikaela   | null       | 50937  |
| 11          | Joziah    | 6          | 28485  |
+-------------+-----------+------------+--------+
Output: 
+-------------+
| employee_id |
+-------------+
| 11          |
+-------------+

Explanation: 
The employees with a salary less than $30000 are 1 (Kalel) and 11 (Joziah).
Kalel's manager is employee 11, who is still in the company (Joziah).
Joziah's manager is employee 6, who left the company because there is no row for employee 6 as it was deleted.

1. salary < 30000인 직원만 추림
2. 그 직원의 manager_id가 Employees 테이블의 employee_id 목록에 없는 경우 (즉, 상사가 없음)만 선택
3. employee_id 기준으로 정렬

select employee_id from employees 
where salary < 30000 and 
manager_id not in (select employee_id from employees) order by employee_id

1. salary < 30000

• 급여가 30,000보다 적은 직원만 필터링합니다.

2. manager_id IS NOT NULL

• 상사가 없는 직원 (예: 최고 관리자)은 제외합니다.
• NULL은 NOT IN 조건과 같이 쓰면 의도치 않게 아무 결과도 안 나올 수 있기 때문에 꼭 필요합니다.

3. manager_id NOT IN (SELECT employee_id FROM Employees)

• 이 서브쿼리는 현재 회사에 남아있는 모든 직원의 ID 목록을 가져옵니다.
• manager_id NOT IN (...) → 이 말은 “이 사람의 상사 ID가 직원 목록에 없다”는 뜻입니다.
• 즉, 퇴사한 상사를 가진 직원이라는 조건이 됩니다.

4. ORDER BY employee_id

• 결과를 employee_id 기준으로 오름차순 정렬합니다.