1378. Replace Employee ID With The Unique Identifier - Detailed Explanation

Free Coding Questions Catalog

Boost your coding skills with our essential coding questions catalog. Take a step towards a better tech career now!

Problem Statement

You have two tables, Employees and EmployeeUNI, in a company database.

Employees
- id (int, primary key)
- name (varchar)
EmployeeUNI
- id (int)
- unique_id (int)

Each row in EmployeeUNI maps an employee’s id to a company‑wide unique_id. Not every employee appears in EmployeeUNI. Write a query that returns each employee’s name alongside their unique_id, or NULL when no mapping exists. The order of rows does not matter.

Examples

Example
Employees

id	name
1	Alice
7	Bob
11	Meir
90	Winston
3	Jonathan

EmployeeUNI

id	unique_id
3	1
11	2
90	3

Result

unique_id	name
NULL	Alice
NULL	Bob
2	Meir
3	Winston
1	Jonathan

Constraints

Total rows in Employees and EmployeeUNI each ≤ 10⁵
Employee id values are unique in Employees
(id, unique_id) pairs are unique in EmployeeUNI

Approach

LEFT JOIN

Start from Employees so every employee appears in the result.
LEFT JOIN EmployeeUNI on matching id.
Select EmployeeUNI.unique_id (will be NULL when no match) and Employees.name.

Why it works

A LEFT JOIN preserves all rows from the left table (Employees). When there is no corresponding id in EmployeeUNI, the unique_id column becomes NULL, satisfying the requirement.

Correlated Subquery (Alternative)

You could also use a subquery to look up each unique_id:

SELECT
  (SELECT unique_id 
   FROM EmployeeUNI u 
   WHERE u.id = e.id) AS unique_id,
  e.name
FROM Employees e;

This returns NULL when the subquery finds no match.

Step‑by‑Step Walkthrough

FROM Employees
‑ take every employee
LEFT JOIN EmployeeUNI ON Employees.id = EmployeeUNI.id
‑ for each employee, pull in their unique_id if it exists
SELECT EmployeeUNI.unique_id, Employees.name
‑ output the two needed columns

For Alice (id = 1), there is no row in EmployeeUNI, so unique_id shows NULL. For Jonathan (id = 3), the join finds unique_id = 1.

Complexity Analysis

Time: O(n) on average for the join, where n is the number of rows in Employees (plus overhead from indexing)
Space: O(1) extra, output size O(n)

Python Code

Python3

. . . .

Java Code

Java

. . . .

SQL Solution

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

Common Mistakes

Using an INNER JOIN instead of LEFT JOIN (drops employees without a unique_id)
Forgetting to alias tables, leading to ambiguous column references
Selecting columns in the wrong order (they asked for unique_id first, then name)

Edge Cases

All employees have no unique_id → every unique_id is NULL
Every employee has a unique_id → behaves like an inner join
Single‑row tables → still returns correct mapping or NULL

Alternative Variations

Return only those with no unique_id (use WHERE EmployeeUNI.id IS NULL)
List employees in ascending order of unique_id, with NULLs first or last (add ORDER BY unique_id)