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 IdentifierInput: Two TablesEmployeesidname1Alice90WinstonEmployeeUNIidunique_id122LEFT JOINON idOutput: Resultunique_idname22AlicenullWinstonKey InsightLEFT JOIN preserves ALL employees from the left tableMissing unique IDs become NULL values
Understanding the Visualization
1
Input Tables
Employees and EmployeeUNI tables
2
LEFT JOIN
Match employees with their unique IDs
3
Output
All employees with unique_id or null
Key Takeaway
🎯 Key Insight: Use LEFT JOIN when you need to preserve all records from the primary table while optionally adding data from a secondary table

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