[SQL 문제 풀이] Employees With Missing Information (누락된 정보가 있는 직원)

Stupefyee's avatar
May 20, 2025
[SQL 문제 풀이] Employees With Missing Information (누락된 정보가 있는 직원)
Employees With Missing Information - LeetCode
Can you solve this real interview question? Employees With Missing Information - Table: Employees +-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | name | varchar | +-------------+---------+ employee_id is the column with unique values for this table. Each row of this table indicates the name of the employee whose ID is employee_id.   Table: Salaries +-------------+---------+ | Column Name | Type | +-------------+---------+ | employee_id | int | | salary | int | +-------------+---------+ employee_id is the column with unique values for this table. Each row of this table indicates the salary of the employee whose ID is employee_id.   Write a solution to report the IDs of all the employees with missing information. The information of an employee is missing if: * The employee's name is missing, or * The employee's salary is missing. Return the result table ordered by employee_id in ascending order. The result format is in the following example.   Example 1: Input: Employees table: +-------------+----------+ | employee_id | name | +-------------+----------+ | 2 | Crew | | 4 | Haven | | 5 | Kristian | +-------------+----------+ Salaries table: +-------------+--------+ | employee_id | salary | +-------------+--------+ | 5 | 76071 | | 1 | 22517 | | 4 | 63539 | +-------------+--------+ Output: +-------------+ | employee_id | +-------------+ | 1 | | 2 | +-------------+ Explanation: Employees 1, 2, 4, and 5 are working at this company. The name of employee 1 is missing. The salary of employee 2 is missing.
Employees With Missing Information - LeetCode
notion image
누락된 정보가 있는 모든 직원의 ID를 보고하는 솔루션을 작성합니다. 다음과 같은 경우 직원의 정보가 누락됩니다: * 직원의 이름이 누락되었거나 * 직원의 급여가 누락되었습니다. 직원_id가 정렬한 결과 테이블을 오름차순으로 반환합니다.
 

내가 작성한 쿼리

Oracle

SELECT employee_id FROM Employees FULL OUTER JOIN Salaries USING (employee_id) WHERE name IS NULL OR salary IS NULL ORDER BY employee_id;

MySQL

SELECT employee_id FROM ( -- Employees 기준 LEFT JOIN SELECT e.employee_id, e.name, s.salary FROM Employees e LEFT JOIN Salaries s ON e.employee_id = s.employee_id UNION -- Salaries 기준 RIGHT JOIN SELECT s.employee_id, e.name, s.salary FROM Employees e RIGHT JOIN Salaries s ON e.employee_id = s.employee_id ) AS combined WHERE name IS NULL OR salary IS NULL ORDER BY employee_id;

차이점

  • FULL OUTER JOIN의 존재 유무
    • Oracle의 경우
      • FULL OUTER JOIN 사용 가능
      • USING (컬럼명) 을 통해 조건 명시
    • MySQL의 경우
      • FULL OUTER JOIN 사용 불가
      • LEFT JOIN, RIGHT JOIN 각각 진행후 UNION으로 합쳐서 구
 
Share article

stupefyee