Replace Employee ID With The Unique Identifier - Problem

You have two tables: Employees and EmployeeUNI.

The Employees table contains employee information with columns:

  • id (int) - Primary key for employee ID
  • name (varchar) - Employee name

The EmployeeUNI table contains unique identifier mappings with columns:

  • id (int) - Employee ID (references Employees.id)
  • unique_id (int) - Corresponding unique identifier

Task: Write a SQL query to show the unique_id and name for each employee. If an employee doesn't have a unique ID in the EmployeeUNI table, show null for their unique_id.

Return the result in any order.

Table Schema

Employees
Column Name Type Description
id PK int Primary key for employee ID
name varchar Employee name
Primary Key: id
EmployeeUNI
Column Name Type Description
id PK int Employee ID (references Employees.id)
unique_id PK int Corresponding unique identifier
Primary Key: (id, unique_id)

Input & Output

Example 1 — Basic Employee Mapping
Input Tables:
Employees
id name
1 Alice
7 Bob
11 Meir
90 Winston
EmployeeUNI
id unique_id
1 22
7 13
11 90
Output:
unique_id name
22 Alice
13 Bob
90 Meir
Winston
💡 Note:

The LEFT JOIN matches employees with their unique IDs. Alice (id=1) gets unique_id=22, Bob (id=7) gets unique_id=13, Meir (id=11) gets unique_id=90. Winston (id=90) has no matching unique_id, so it shows null.
Example 2 — All Employees Have Unique IDs
Input Tables:
Employees
id name
1 John
2 Jane
EmployeeUNI
id unique_id
1 100
2 200
Output:
unique_id name
100 John
200 Jane
💡 Note:

When all employees have corresponding unique IDs, the LEFT JOIN works like an INNER JOIN, returning all employees with their unique identifiers.
Example 3 — No Employees Have Unique IDs
Input Tables:
Employees
id name
5 Sarah
6 Mike
EmployeeUNI
id unique_id
Output:
unique_id name
Sarah
Mike
💡 Note:

When no employees have unique IDs in the EmployeeUNI table, all employees are still returned with null values for unique_id.

Constraints

  • 1 ≤ Employees.id ≤ 10^5
  • 1 ≤ EmployeeUNI.unique_id ≤ 10^5
  • Each row in EmployeeUNI has a unique (id, unique_id) combination

Visualization

Tap to expand
Replace Employee ID With Unique Identifier INPUT TABLES Employees id name 1 Alice 7 Bob 11 Meir 90 Winston 3 Jonathan EmployeeUNI id unique_id 3 1 11 2 90 3 ALGORITHM STEPS 1 Use LEFT JOIN Keep all employees even without unique_id 2 Join on id column Employees.id = EmployeeUNI.id 3 Select columns unique_id, name 4 NULL for no match Unmatched rows get NULL unique_id SELECT u.unique_id, e.name FROM Employees e LEFT JOIN EmployeeUNI u ON e.id = u.id FINAL RESULT unique_id name null Alice null Bob 2 Meir 3 Winston 1 Jonathan Join Visualization Emp UNI Match LEFT JOIN keeps all from left table Key Insight: LEFT JOIN is essential when you need all records from the primary table (Employees) regardless of whether a matching record exists in the secondary table (EmployeeUNI). Unmatched rows return NULL for the columns from the right table, preserving the complete employee list. TutorialsPoint - Replace Employee ID With The Unique Identifier | LEFT JOIN Approach

Related Problems

Asked in
Amazon 12 Google 8 Microsoft 6
45.2K Views
High Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen