Replace Employee ID With The Unique Identifier

Replace Employee ID With The Unique Identifier

What is this problem about?

The Replace Employee ID With The Unique Identifier interview question is a database join problem. You are given two tables: one containing employee IDs and names, and another mapping employee IDs to unique identifiers. The task is to return a result table showing each employee's unique identifier alongside their name, including employees who have no unique identifier (shown as NULL).

Why is this asked in interviews?

This SQL problem is asked at Uber, Microsoft, Infosys, Meta, Amazon, Google, Bloomberg, and Adobe because it tests the fundamental understanding of LEFT JOIN in SQL. LEFT JOIN is one of the most commonly used operations in real-world data engineering and analytics pipelines. It evaluates whether candidates understand the difference between INNER JOIN (only matching rows) and LEFT JOIN (all rows from the left table, NULLs for non-matches).

Algorithmic pattern used

The pattern is a LEFT JOIN between the Employees table and the EmployeeUNI table on the id column. A LEFT JOIN ensures all employees appear in the result, even those without a unique identifier. The unique ID column will contain NULL for those employees. The SQL structure is:

SELECT eu.unique_id, e.name
FROM Employees e
LEFT JOIN EmployeeUNI eu ON e.id = eu.id

This is O(n) in practice with proper indexing on the join key.

Example explanation

Employees table:

id	name
1	Alice
2	Bob
3	Carol

EmployeeUNI table:

id	unique_id
1	101
3	103

LEFT JOIN result:

unique_id	name
101	Alice
NULL	Bob
103	Carol

Bob has no unique identifier → NULL. Alice and Carol are matched.

Common mistakes candidates make

• Using INNER JOIN instead of LEFT JOIN, which excludes employees without a unique ID.
• Joining on the wrong column — make sure to join on the employee id, not the unique_id.
• Selecting columns from the wrong table (e.g., selecting e.id instead of eu.unique_id).
• Forgetting to alias tables, leading to ambiguous column references.

Interview preparation tip

For the Replace Employee ID With The Unique Identifier coding problem, the database interview pattern is a straightforward LEFT JOIN. Practice writing LEFT JOIN queries for scenarios with missing data, as NULL-handling is essential in real analytics work. Interviewers at Uber and Adobe often follow up with "how would the result change with INNER JOIN?" or "how do you handle NULL in aggregations?" — be ready with clear, concise answers.