Replace Employee ID With The Unique Identifier

Replace Employee ID With The Unique Identifier - Problem

Database Easy

You're working at a company that's migrating from an old employee ID system to a new unique identifier system. Your task is to map employee information from the old system to the new one.

You have two tables:

Employees: Contains employee id and name
EmployeeUNI: Contains the mapping between old id and new unique_id

Your goal is to create a report that shows each employee's unique_id and name. If an employee doesn't have a unique_id in the new system yet, show null for their unique_id.

This is a classic LEFT JOIN scenario where we want all employees, whether they have unique IDs or not.

Input & Output

example_1.sql — Basic Example

$ Input: Employees table: | id | name | |----|-------| | 1 | Alice | | 7 | Bob | | 11 | Meir | EmployeeUNI table: | id | unique_id | |----|----------| | 1 | 101 | | 7 | 102 |

› Output: | unique_id | name | |-----------|-------| | 101 | Alice | | 102 | Bob | | null | Meir |

💡 Note: Alice (ID=1) maps to unique_id 101, Bob (ID=7) maps to unique_id 102, and Meir (ID=11) has no mapping so gets null

example_2.sql — All Employees Have Unique IDs

$ Input: Employees table: | id | name | |----|------| | 1 | John | | 2 | Jane | EmployeeUNI table: | id | unique_id | |----|----------| | 1 | 201 | | 2 | 202 |

› Output: | unique_id | name | |-----------|------| | 201 | John | | 202 | Jane |

💡 Note: All employees have corresponding unique IDs, so no null values in result

example_3.sql — No Employees Have Unique IDs

$ Input: Employees table: | id | name | |----|-------| | 1 | Alice | | 2 | Bob | EmployeeUNI table: | id | unique_id | |----|----------| (empty)

› Output: | unique_id | name | |-----------|-------| | null | Alice | | null | Bob |

💡 Note: No employees have unique IDs yet, so all get null values while preserving their names

Visualization

Tap to expand

Understanding the Visualization

All Members Preserved

LEFT JOIN ensures every employee appears in results

Match Where Possible

When unique_id exists, include it in the result

Fill Gaps with NULL

When no unique_id exists, use null placeholder

Key Takeaway

🎯 Key Insight: LEFT JOIN is perfect when you need ALL records from one table, with optional matching data from another table

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through both tables with hash join optimization

✓ Linear Growth

Space Complexity

O(n)

Space for hash table and result set

⚡ Linearithmic Space

Constraints

1 ≤ employees.length ≤ 1000
1 ≤ employeeUNI.length ≤ 1000
1 ≤ id ≤ 10⁶
Each row in EmployeeUNI has unique (id, unique_id) pairs
Employee names contain only English letters and spaces

Asked in

a Amazon 23 ⊞ Microsoft 18 G Google 15 f Meta 12

The optimal solution uses a LEFT JOIN to combine the Employees and EmployeeUNI tables. This preserves all employee records while adding unique_id information where available, resulting in O(n) time complexity with efficient database optimization.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Query Approach	O(n²)	O(1)	Use a subquery to find unique_id for each employee
LEFT JOIN (Optimal)	O(n)	O(n)	Use LEFT JOIN to efficiently combine both tables in one query

Nested Query Approach — Algorithm Steps

Select all employees from the Employees table
For each employee, use a subquery to find their unique_id
Return the unique_id if found, otherwise return null

Visualization

Tap to expand

Step-by-Step Walkthrough

Select Employee

Pick first employee from Employees table

Execute Subquery

Search EmployeeUNI table for matching ID

Repeat Process

Repeat for each subsequent employee

Code -

solution.c — C

// SQL solution using subquery
char* query = "\
SELECT \
    (SELECT unique_id \
     FROM EmployeeUNI u \
     WHERE u.id = e.id) AS unique_id, \
    e.name \
FROM Employees e \
ORDER BY e.name\
";

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of n employees, we search through m unique_id records

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond result set

✓ Linear Space

Constraints

1 ≤ employees.length ≤ 1000
1 ≤ employeeUNI.length ≤ 1000
1 ≤ id ≤ 10⁶
Each row in EmployeeUNI has unique (id, unique_id) pairs
Employee names contain only English letters and spaces

52.0K Views

High Frequency

~8 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Nested Query Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler