DEV Community

Retiago Drago

Posted on • Updated on

Replace Employee ID With The Unique Identifier | LeetCode | MSSQL

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