알고리즘

[SQL] 1378. Replace Employee ID With The Unique Identifier (Easy)

파뱁 2024. 12. 31. 13:28
728x90

조인 사용하는 문제로 넘어왔다.

https://leetcode.com/problems/replace-employee-id-with-the-unique-identifier/submissions/1493012049/?envType=study-plan-v2&envId=top-sql-50

⬆️ 문제 전문은 위 링크에서

 

Table: Employees

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table contains the id and the name of an employee in a company.
 

Table: EmployeeUNI

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| unique_id     | int     |
+---------------+---------+
(id, unique_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table contains the id and the corresponding unique id of an employee in the company.
 

Write a solution to show the unique ID of each user, If a user does not have a unique ID replace just show null.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Employees table:
+----+----------+
| id | name     |
+----+----------+
| 1  | Alice    |
| 7  | Bob      |
| 11 | Meir     |
| 90 | Winston  |
| 3  | Jonathan |
+----+----------+
EmployeeUNI table:
+----+-----------+
| id | unique_id |
+----+-----------+
| 3  | 1         |
| 11 | 2         |
| 90 | 3         |
+----+-----------+
Output: 
+-----------+----------+
| unique_id | name     |
+-----------+----------+
| null      | Alice    |
| null      | Bob      |
| 2         | Meir     |
| 3         | Winston  |
| 1         | Jonathan |
+-----------+----------+
Explanation: 
Alice and Bob do not have a unique ID, We will show null instead.
The unique ID of Meir is 2.
The unique ID of Winston is 3.
The unique ID of Jonathan is 1.

 

 

너무 오랜만에 아우터 조인을 사용해봐서 좀 헷갈렸다.

unique_id 가 null 이어도 name은 다 나와야 하기 때문에

Employees E, EmployeeUNI U 이렇게 두 개의 테이블이 있다면

E기준으로 left join을 진행해야 이름이 전부 나온다.

이때의 조건은 각 테이블의 id 컬럼이 동일하다는 조건이기 때문에

 

Employees E left join EmployeeUNI U on E.id = U.id

 

를 from 절에 사용해주면 된다.

 

참고: left outer join

https://www.w3schools.com/sql/sql_join_left.asp

 

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

문제 풀이는 다음과 같다.

select U.unique_id, E.name
from Employees E left join EmployeeUNI U on E.id = U.id;
728x90
반응형