Replace Employee ID With The Unique Identifier | LeetCode | MSSQL
Tags: leetcode, mssql, tutorial, beginners
Published: 2023-06-23 17:24:00

## 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.

```sql
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.

![s1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kq36y2r17fxkrdxqgd37.png)

### 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).

```sql
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.

![s2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/imdw8gpfasmo0ad2vhun.png)

### 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.

```sql
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.

![s3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bfd06rjlzb55ixi99w1w.png)

### Source Code 4

The fourth solution is similar to the second approach but without the `ISNULL` function, assuming that `Employees` is complete.

```sql
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.

![s4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/178uo9c6whf1fwuep30i.png)

## 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](https://leetcode.com/problems/replace-employee-id-with-the-unique-identifier/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}