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.
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.
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.
SQL
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.
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.
Python
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.
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.
SQL
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.
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.
SQL
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.
MySQL
| Approach | Complexity |
|---|---|
| SQL Join Operation | Time Complexity: O(n), where n is the number of rows in the |
| Nested Loop Simulation Using Language Constructs | Time Complexity: O(n + m), where n and m are lengths of |
| 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 | — |
| 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 |
6. Replace Employee ID With The Unique Identifier | SQL Leetcode • Start Practicing • 21,508 views views
Watch 9 more video solutions →Practice Replace Employee ID With The Unique Identifier with our built-in code editor and test cases.
Practice on FleetCode