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

Stupefyee's avatar
Stupefyee
May 20, 2025
[SQL 문제 풀이] Employees With Missing Information (누락된 정보가 있는 직원)
Contents
내가 작성한 쿼리
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
https://leetcode.com/problems/employees-with-missing-information/description/
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

More articles

See more posts

[JAVA 문제 풀이] 268. 숫자 문자열과 영단어

May 21, 2025
[JAVA 문제 풀이] 268. 숫자 문자열과 영단어

[SQL 문제 풀이] Exchange Seats (교환석)

May 20, 2025
[SQL 문제 풀이] Exchange Seats (교환석)

[JAVA 문제 풀이] 267. 구명보트

May 20, 2025
[JAVA 문제 풀이] 267. 구명보트

[JAVA 문제 풀이] 266. K번째수

May 20, 2025
[JAVA 문제 풀이] 266. K번째수

stupefyee

RSS·Powered by Inblog