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

Problem Statement

You have two tables:
Employees

Column	Type
id	int
name	varchar

id is the primary key.

EmployeeUNI

Column	Type
id	int
unique_id	int

(id, unique_id) is the primary key.

Write a query that returns each employee’s unique_id alongside their name. If an employee does not have a corresponding unique_id, show NULL. The order of rows does not matter.

Examples

Example 1
Employees:

id	name
1	Alice
7	Bob
11	Meir
90	Winston
3	Jonathan
EmployeeUNI:
id	unique_id
----	-----------
3	1
11	2
90	3
Output
unique_id	name
-----------	----------
NULL	Alice
NULL	Bob
2	Meir
3	Winston
1	Jonathan

Example 2
Employees:

id	name
1	Tom
2	Jane
EmployeeUNI: (empty)
Output
unique_id	name
-----------	------
NULL	Tom
NULL	Jane

Example 3
Employees:

id	name
5	Richard
6	Samantha
EmployeeUNI:
id	unique_id
----	-----------
5	10
6	20
Output
unique_id	name
-----------	-----------
10	Richard
20	Samantha

Constraints

Each id in Employees is unique.
Each row in EmployeeUNI maps an existing employee to exactly one unique_id.
Tables may each contain up to a few thousand rows.
You may return results in any order.

Hints

Which type of JOIN returns all rows from Employees even when there’s no matching row in EmployeeUNI?
How does SQL represent missing values when a join has no match?

Solution Approach

We need every employee, regardless of whether they have a unique identifier. An INNER JOIN would drop employees without a match. A LEFT JOIN preserves all employees and yields NULL for missing unique_ids.

SQL Query

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

Explanation

FROM Employees AS e
Start with Employees as the left table alias e.
LEFT JOIN EmployeeUNI AS u ON e.id = u.id
Match each employee’s id to EmployeeUNI.id.
- If a match exists, u.unique_id is filled.
- Otherwise, u.unique_id is NULL.
SELECT u.unique_id, e.name
Output the unique identifier first, then the employee’s name.

Complexity Analysis

Time: O(N + M) for scanning both tables and performing the join (N = #Employees, M = #EmployeeUNI).
Space: O(N + M) for any intermediate join structures.

Step‑by‑Step Walkthrough

Given
Employees:

id	name
1	Alice
2	Bob
EmployeeUNI:
id	unique_id
----	-----------
2	42

Step 1: Read Employees row (1, Alice). No EmployeeUNI row with id=1 → unique_id becomes NULL.
Step 2: Read Employees row (2, Bob). Find EmployeeUNI row with id=2 → unique_id=42.
Result:
unique_id name
NULL Alice
42 Bob

unique_id	name
NULL	Alice
42	Bob

Python Code

Python3

. . . .

Java Code

Java

. . . .

Common Mistakes

Using INNER JOIN (drops employees without a unique_id).
Swapping column order (selecting name before unique_id).
Forgetting to alias tables, leading to ambiguous column references.

Edge Cases

No Employees: returns an empty result set.
No EmployeeUNI entries: every unique_id is NULL.
Every employee has a unique_id: same as an INNER JOIN result.

Alternative Variations

Right Join (in SQL dialects that support it): swap table positions.
Full Outer Join to find employees without unique_ids and unique_ids without employees (if data were inconsistent).
Using COALESCE(u.unique_id, 0) to replace NULLs with a default value like 0.