The Problem
This problem involves two tables, Employees and EmployeeUNI.
Table: Employees
| Column Name | Type |
|---|---|
| id | int |
| name | varchar |
In this table, id is the primary key. Each row contains the id and the name of an employee in a company.
Table: EmployeeUNI
| Column Name | Type |
|---|---|
| id | int |
| unique_id | int |
The composite (id, unique_id) is the primary key for this table. Each row contains the id and the corresponding unique id of an employee in the company.
The task is to write an SQL query to show the unique ID of each user. If a user does not have a unique ID, just show NULL. The result can be returned in any order.
Explanation
Let’s consider the following input data:
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 |
The unique ID of Meir is 2, Winston is 3, and Jonathan is 1. Alice and Bob do not have unique IDs.
Hence, the expected output is:
| unique_id | name |
|---|---|
| NULL | Alice |
| NULL | Bob |
| 2 | Meir |
| 3 | Winston |
| 1 | Jonathan |
The Solution
There are several ways to solve this problem. We will be discussing four different approaches that use different types of JOIN operations and NULL handling. Each method has its own advantages and disadvantages in terms of readability, scalability, and execution speed.
Source Code 1
The first method uses a LEFT JOIN operation from Employees to EmployeeUNI on id. If an employee doesn’t have a unique id (i.e., no corresponding row in EmployeeUNI), ISNULL function replaces the missing unique_id with NULL.
SELECT
u.unique_id,
ISNULL(e.name, NULL) [name]
FROM Employees e LEFT JOIN EmployeeUNI u ON u.id = e.id
The runtime for this solution is 3010ms, beating 5.40% of submissions on LeetCode.
Source Code 2
The second approach uses a RIGHT JOIN from EmployeeUNI to Employees on id. The ISNULL function is used to handle the missing names, which might not be necessary if Employees is complete (i.e., all employees are included in the Employees table).
SELECT
u.unique_id,
ISNULL(e.name, NULL) [name]
FROM EmployeeUNI u RIGHT JOIN Employees e ON u.id = e.id
This solution performs faster than the previous one, with a runtime of 2277ms, beating 26.40% of submissions on LeetCode.
Source Code 3
The third approach uses a similar LEFT JOIN operation as the first one but omits the ISNULL function, assuming that Employees is complete.
SELECT
eu.unique_id,
e.name
FROM Employees e LEFT JOIN EmployeeUNI eu ON e.id = eu.id
This solution has a runtime of 3314ms, beating 5.2% of submissions on LeetCode.
Source Code 4
The fourth solution is similar to the second approach but without the ISNULL function, assuming that Employees is complete.
SELECT
u.unique_id,
e.name
FROM EmployeeUNI u RIGHT JOIN Employees e ON u.id = e.id
This solution has a runtime of 2093ms, beating 43.45% of submissions on LeetCode.
Conclusion
Each of the above solutions provides a valid answer to the problem, albeit with different performance results on LeetCode.
According to the LeetCode metrics, the fourth solution (Source Code 4) performs the best, followed by the second solution (Source Code 2), then the first solution (Source Code 1), and finally, the third solution (Source Code 3). However, it’s important to note that in real-world scenarios, the performance might vary based on the specific RDBMS and the database structure and data volume.
You can find the original problem at LeetCode.
For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.
ranggakd – Link in Bio & Creator Tools | Beacons
@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.
beacons.ai
