Replace Employee ID With The Unique Identifier - Video Solutions

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.