SQL JOIN語法 用UID取代員工ID Leetcode #1378

Mediamodifier null on Unsplash

題目敘述

題目會給我們兩張資料表。

裡面分別有id、name等欄位。這張資料表的id是主鍵。

第二張資料表是EmployeeUNI

裡面分別有id、unique_id等欄位。這張資料表的(id、unique_id)是複合主鍵id。

題目要求我們列出每位員工所對應到的unique_id，如果對應不到，則填上null

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.

詳細的題目可在這裡看到

約束條件

列出每位員工所對應到的unique_id，如果對應不到，則填上null

演算法

Employees 和 ​EmployeeUNI 做 LEFT JOIN ​

連接的條件為 e.id = e_uni.id

假如第一次接觸SQL或者JOIN語法的同學，可參考 LEFT JOIN 語法教學。

程式碼

SELECT e_uni.unique_id, e.name
FROM Employees e LEFT JOIN EmployeeUNI e_uni
ON e.id = e_uni.id;

Reference:

[1] MySQL by LEFT JOIN - Replace Employee ID With The Unique Identifier - LeetCode