Replace Employee ID With The Unique Identifier - Solution & Explanation

EasyDatabase6 min readAsked at: Amazon, Microsoft, Meta +6

Problem Statement

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.

Approach Overview

Problem Overview: You are given two tables: Employees and EmployeeUNI. The goal is to replace each employee's id with their corresponding unique_id. If an employee does not have a unique identifier, the result should still include the employee name with a NULL unique ID. The task is essentially a relational lookup between two tables.

Approach 1: Nested Loop Simulation Using Language Constructs (O(n * m) time, O(1) space)

This approach mimics a brute-force join using standard programming constructs. Iterate through every row in the Employees list and, for each employee, scan the entire EmployeeUNI dataset to find a matching id. Once found, output the mapped unique_id and employee name. If no match exists, return NULL for the identifier. This method demonstrates the core logic behind relational joins but performs poorly as data grows because each lookup requires scanning the entire secondary table.

Approach 2: SQL Join Operation (O(n + m) time, O(1) extra space)

The natural solution uses a database join between the two tables. SQL engines optimize join execution internally using indexes or hash joins. You match Employees.id with EmployeeUNI.id and select the unique_id alongside the employee name. This eliminates repeated scanning and lets the database engine efficiently combine rows. This approach directly leverages relational database design and is the most idiomatic solution for problems under the database category.

Approach 3: Inner Join Approach (O(n + m) time, O(1) extra space)

An INNER JOIN retrieves only employees that have a corresponding record in EmployeeUNI. The database scans both tables and returns rows where the join condition matches. While efficient, it excludes employees without a unique identifier. This approach is useful when the requirement explicitly states that only matched records should appear. The operation relies on standard SQL join mechanics.

Approach 4: Left Join Approach (O(n + m) time, O(1) extra space)

The LEFT JOIN approach returns all employees from the Employees table and attaches matching identifiers from EmployeeUNI when available. If no match exists, SQL fills the unique_id column with NULL. This matches the exact requirement of the problem and is the most reliable query pattern for preserving all employee records while performing the mapping.

Recommended for interviews: The expected solution is the LEFT JOIN. It demonstrates understanding of relational joins and ensures every employee appears in the result set even without a matching identifier. The nested loop approach shows the conceptual mechanics of joining datasets, but the SQL join solution proves practical database query skills.

Approach 1: SQL Join Operation

This approach involves using SQL join operations to combine data from the Employees and EmployeeUNI tables to generate the desired result. Specifically, a LEFT JOIN can be used here to ensure that all employees are included in the result, even if they do not have a corresponding unique ID.

In this SQL query, we perform a LEFT JOIN between the Employees table and the EmployeeUNI table on the matching id field. This ensures that all records from Employees are retained in the result, and the unique_id from EmployeeUNI is retrieved where available. If there is no match, unique_id is null.

Code

SQL

Complexity

Time Complexity: O(n), where n is the number of rows in the Employees table.
Space Complexity: O(m + n), where m and n are the number of rows in the Employees and EmployeeUNI tables, respectively.

Try this approach in the editor →

Approach 2: Nested Loop Simulation Using Language Constructs

Another approach involves simulating SQL-like operations using constructs provided by programming languages. This essentially involves iterating over both tables and manually matching entries based on the id column.

In this Python example, we first create a dictionary id_to_unique_id to map each id to its unique_id from the employee_uni list. We then iterate over the employees list, checking for each employee's id in the dictionary. If it exists, we extract the unique_id; otherwise, we use None. Finally, we build a list of dictionaries to hold the result.

Code

Python

Complexity

Time Complexity: O(n + m), where n and m are lengths of employees and employee_uni, respectively.
Space Complexity: O(n + m), as we store all employees and the mapping dictionary in memory.

Try this approach in the editor →

Approach 3: Inner Join Approach

This approach involves using an INNER JOIN to combine the Employees and EmployeeUNI tables based on the employee ID. However, INNER JOIN will only include rows that have matches in both tables. To account for employees without a unique ID, we can consider using an OUTER JOIN as described in the next approach.

This SQL statement selects the employee ID, name, and unique ID for employees that have a corresponding entry in the EmployeeUNI table.

Code

SQL

Complexity

The time complexity is O(n) because the SQL engine needs to scan both tables. The space complexity depends on the SQL engine's implementation.

Try this approach in the editor →

Approach 4: Left Join Approach

The more suitable approach is the LEFT JOIN because it allows for including all records from the Employees table, and the matching records from the EmployeeUNI table. If there is no match, the result is NULL which solves the requirement of showing 'null' for employees without a unique ID.

This SQL statement joins the Employees table with EmployeeUNI using a LEFT JOIN, ensuring all employees are listed with their unique ID if available, or NULL otherwise.

Code

SQL

Complexity

The time complexity for a LEFT JOIN is generally O(n + m) where n and m are the number of rows in the Employees and EmployeeUNI tables respectively. Space complexity also depends on implementation details in the SQL engine.

Try this approach in the editor →

Approach 5: Default Approach

Code

MySQL

Try this approach in the editor →

Complexity Comparison

Approach	Complexity
SQL Join Operation	Time Complexity: O(n), where n is the number of rows in the `Employees` table. Space Complexity: O(m + n), where m and n are the number of rows in the `Employees` and `EmployeeUNI` tables, respectively.
Nested Loop Simulation Using Language Constructs	Time Complexity: O(n + m), where n and m are lengths of `employees` and `employee_uni`, respectively. Space Complexity: O(n + m), as we store all employees and the mapping dictionary in memory.
Inner Join Approach	The time complexity is O(n) because the SQL engine needs to scan both tables. The space complexity depends on the SQL engine's implementation.
Left Join Approach	The time complexity for a LEFT JOIN is generally O(n + m) where n and m are the number of rows in the Employees and EmployeeUNI tables respectively. Space complexity also depends on implementation details in the SQL engine.
Default Approach	—

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Nested Loop Simulation	O(n * m)	O(1)	Conceptual understanding of how joins work without database operations
SQL Join Operation	O(n + m)	O(1)	General database solution where tables must be combined by key
Inner Join	O(n + m)	O(1)	When only matched records between both tables should appear
Left Join	O(n + m)	O(1)	Best choice when all employees must appear even if no identifier exists

Video Solution

6. Replace Employee ID With The Unique Identifier | SQL Leetcode • Start Practicing • 24,283 views views

Watch 9 more video solutions →

Frequently Asked Questions

Replace Employee ID With The Unique Identifier Python solution

In Python, you can simulate the join by building a dictionary mapping id to unique_id from EmployeeUNI. Then iterate through Employees and retrieve the identifier with a constant-time dictionary lookup. This reduces the complexity to O(n + m) with O(m) extra space for the map.

Is Replace Employee ID With The Unique Identifier easy or hard?

The problem is classified as Easy because it focuses on a single SQL join concept. Once you understand how LEFT JOIN preserves rows from the left table while attaching matches from the right table, the query becomes straightforward.

How to solve Replace Employee ID With The Unique Identifier in O(n)?

Use a SQL LEFT JOIN to match Employees.id with EmployeeUNI.id. The database engine performs the lookup using optimized join algorithms such as hash joins or indexed joins. This avoids scanning the second table repeatedly and processes both datasets in roughly linear time relative to their size.

What is the best approach for Replace Employee ID With The Unique Identifier?

The best approach is a SQL LEFT JOIN between the Employees and EmployeeUNI tables. This ensures every employee appears in the output while attaching the corresponding unique_id when it exists. If no match is found, SQL automatically returns NULL for the identifier. The query runs in roughly O(n + m) time depending on database join optimization.

Is Replace Employee ID With The Unique Identifier asked at Google/Amazon/Meta?

Database join questions similar to this problem appear frequently in interviews at companies like Amazon, Meta, and Google, especially for data engineering and backend roles. Interviewers often test whether candidates understand INNER JOIN vs LEFT JOIN behavior and when to preserve unmatched rows.

What data structure is used in Replace Employee ID With The Unique Identifier?

The core concept is a relational database join rather than a traditional data structure. Internally, SQL engines often use hash tables or indexed lookups to match keys between tables. When simulated in code, the problem resembles mapping keys using a dictionary or hash map.

What is the time complexity of Replace Employee ID With The Unique Identifier?

Using a SQL join, the effective complexity is about O(n + m), where n is the number of rows in Employees and m is the number of rows in EmployeeUNI. Modern database engines optimize joins using hashing or indexing. A brute force nested loop simulation would take O(n * m) time.

Ready to solve this problem?

Practice Replace Employee ID With The Unique Identifier with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyEasy

Acceptance83.6%

Approaches5

Reading time6 min

Asked at

Amazon Microsoft Meta Infosys Cognizant

Practice this problem

Open in Editor

Problem Statement

Approach Overview

Approach 1: SQL Join Operation

Code

Complexity

Approach 2: Nested Loop Simulation Using Language Constructs

Code

Complexity

Approach 3: Inner Join Approach

Code

Complexity

Approach 4: Left Join Approach

Code

Complexity

Approach 5: Default Approach

Code

Complexity Comparison

Detailed Complexity Analysis

Video Solution

Frequently Asked Questions

Related Problems

Ready to solve this problem?

Problem Info

Table of Contents