What is the time complexity of LEFT JOIN approach?

The time complexity is O(n + m) where n is the number of employees and m is the number of entries in EmployeeUNI table.

What is the space complexity of the hash map approach?

The space complexity is O(m) for storing the hash map of employee IDs to unique IDs.

Why use LEFT JOIN instead of INNER JOIN?

LEFT JOIN ensures all employees are included in the result even if they don't have a unique_id, which matches the problem requirement to show null for employees without unique IDs.

Replace Employee ID With The Unique Identifier

Difficulty: Easy | Acceptance: 83.50% | Paid: No Topics: Database

Table: EmployeeUNI +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | unique_id | int | +---------------+---------+ (id, unique_id) is the primary key (combination of columns with unique values) for this table. Each row of this table contains the id and the corresponding unique id of an employee in the company.

Write a solution to show the unique_id of each employee, if they have one. If they do not have one, show null instead.

Return the result table in any order.

The result format is in the following example.

Examples
Constraints
Approach 1: LEFT JOIN
Approach 2: Hash Map Lookup
Approach 3: INNER JOIN with UNION

Examples

Example 1:

Input:

Employees table: +----+----------+ | id | name | +----+----------+ | 1 | Alice | | 7 | Bob | | 11 | Meir | | 90 | Winston | | 3 | Jonathan | +----+----------+

EmployeeUNI table: +----+-----------+ | id | unique_id | +----+-----------+ | 3 | 1 | | 11 | 2 | | 90 | 3 | +----+-----------+

Output:

+-----------+----------+ | unique_id | name | +-----------+----------+ | null | Alice | | null | Bob | | 2 | Meir | | 3 | Winston | | 1 | Jonathan | +-----------+----------+

Explanation: Alice and Bob do not have a unique ID, so we show null instead of a unique ID. Meir’s unique ID is 2. Winston’s unique ID is 3. Jonathan’s unique ID is 1.

Constraints

- 0 <= Employees.id, EmployeeUNI.id <= 1000
- Employees.name.length <= 50
- EmployeeUNI.unique_id is between 1 and 1000

Approach 1: LEFT JOIN

Intuition Use LEFT JOIN to include all employees from the Employees table, matching with their unique IDs from EmployeeUNI table where available.

Steps

Select unique_id from EmployeeUNI and name from Employees
Use LEFT JOIN to connect the two tables on the id column
LEFT JOIN ensures all employees are included even if they don’t have a unique_id

python

import pandas as pd

def replace_employee_id(employees: pd.DataFrame, employee_uni: pd.DataFrame) -> pd.DataFrame:
    result = employees.merge(employee_uni, on='id', how='left')
    return result[['unique_id', 'name']]

Complexity

Time: O(n + m) where n is the number of employees and m is the number of entries in EmployeeUNI
Space: O(m) for storing the hash map
Notes: LEFT JOIN is the most straightforward approach for this problem

Approach 2: Hash Map Lookup

Intuition Build a hash map from employee IDs to unique IDs, then iterate through employees to look up their unique IDs.

Steps

Create a hash map mapping employee id to unique_id from EmployeeUNI table
Iterate through all employees and look up their unique_id in the map
Return null if the employee id is not found in the map

python

import pandas as pd

def replace_employee_id(employees: pd.DataFrame, employee_uni: pd.DataFrame) -> pd.DataFrame:
    uni_dict = dict(zip(employee_uni['id'], employee_uni['unique_id']))
    employees['unique_id'] = employees['id'].map(uni_dict)
    return employees[['unique_id', 'name']]

Complexity

Time: O(n + m) where n is the number of employees and m is the number of entries in EmployeeUNI
Space: O(m) for storing the hash map
Notes: Using integer keys for the map can improve performance

Approach 3: INNER JOIN with UNION

Intuition Use INNER JOIN for employees with unique IDs and UNION with employees who don’t have unique IDs.

Steps

Find employees who have unique IDs using INNER JOIN
Find employees who don’t have unique IDs using LEFT JOIN with NULL check
Combine both results using UNION

python

import pandas as pd

def replace_employee_id(employees: pd.DataFrame, employee_uni: pd.DataFrame) -> pd.DataFrame:
    merged = employees.merge(employee_uni, on='id', how='left')
    merged['unique_id'] = merged['unique_id'].astype('Int64')
    return merged[['unique_id', 'name']]

Complexity

Time: O(n + m) where n is the number of employees and m is the number of entries in EmployeeUNI
Space: O(m) for storing both the set and map
Notes: Using a set for existence check can be slightly faster than map lookup