Replace Employee ID With The Unique Identifier | Leetcode 1378

Welcome to the foundational entry point of our SQL Joins series! In this chapter, we transition from basic SELECT statements into relational database querying using joins. We explore LeetCode 1378: Replace Employee ID With The Unique Identifier, breaking down how to combine data from multiple tables while preserving records from a primary reference table even when matching records do not exist.

1. Problem Statement & Database Schema

In enterprise database design, data is often normalized across multiple tables to avoid redundancy. This problem provides two distinct tables containing employee records: the primary Employees table and the secondary EmployeeUNI table.

Table 1: Employees

Table 2: EmployeeUNI

Note: The combination of (id, unique_id) forms the composite primary key for the EmployeeUNI table.

Objective

Write a SQL solution to display the unique_id and name of each user. If a user does not possess a corresponding unique_id in the secondary table, the query must return NULL for that user. The result can be returned in any order.

2. Core Mental Model & Real-Life Analogy

Real-Life Analogy: The Employee Badge Registry

Imagine an organization where every employee (Employees) has a permanent internal identification number and a name. However, building access badges (EmployeeUNI) are issued separately.

• Every employee must appear on the company roster, whether they have been issued a special electronic gate badge or not.
• Some employees have electronic gate badges (unique_id), while newer or temporary employees do not.
• When printing the master security report, we must list all employees and match their electronic badge numbers if available. If an employee lacks an electronic badge, we leave that column blank (NULL) rather than omitting the employee from the report entirely.

3. Why Do We Need a LEFT JOIN?

When combining two tables, choosing the correct join type is critical to the output structure. Let's analyze our two key requirements:

1. We need every employee's name. This means our result set must be anchored by the complete set of rows in the Employees table.
2. We only need matching unique IDs. If an employee ID exists in Employees but not in EmployeeUNI, we still want the employee name, accompanied by a NULL value for the missing unique identifier.

Using an INNER JOIN would drop any employee who lacks an entry in EmployeeUNI (such as Alice and Bob in our dataset). Therefore, a LEFT JOIN is required.

Visualizing Table Positions in a LEFT JOIN

• Left Table (Employees): Every row is preserved in the final output.
• Right Table (EmployeeUNI): Only rows matching the join condition are merged. Unmatched rows result in NULL values for the right-side columns.

4. Understanding Table Aliasing (AS)

Writing out fully-qualified table names repeatedly (e.g., Employees.id and EmployeeUNI.unique_id) makes queries verbose and hard to read. Aliasing allows us to assign brief, convenient nicknames to tables in our query scope.

• Employees AS e shortens references to e.
• EmployeeUNI AS eu shortens references to eu.

5. Step-by-Step Query Construction

Let's build the SQL query incrementally, moving from the data source definition down to the selected output columns.

Step A: Define the Data Sources and Join Strategy

We specify our left table (Employees), apply the LEFT JOIN keyword, and connect the right table (EmployeeUNI) using the ON clause matching the common id column.

FROM Employees AS e
LEFT JOIN EmployeeUNI AS eu
  ON e.id = eu.id

Step B: Select the Required Columns

The problem asks for unique_id first, followed by name. We fetch these using our table aliases. We also use the AS keyword to explicitly set the output column headers.

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

6. Complete Production-Ready SQL Query

Here is the complete, executed solution running against our database schema:

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

Note: In this specific LeetCode problem, the target column names (unique_id and name) match the source column names. However, utilizing explicit column aliases (AS) is an industry best practice when dealing with multi-table joins, preventing ambiguity and supporting requirement changes where output headers must differ from raw database columns.

7. Execution Walkthrough with Sample Data

Let's trace how the database engine processes our tables during query execution.

Input Data

Employees Table (e)

EmployeeUNI Table (eu)

Execution Trace

1. Scan Left Table (Employees): The engine iterates through all 5 employee records (Alice, Bob, Meir, Winston, Jonathan).
2. Evaluate LEFT JOIN against EmployeeUNI:
   • Alice (id = 1): No match found in EmployeeUNI. Result: unique_id = NULL, name = 'Alice'.
   • Bob (id = 7): No match found in EmployeeUNI. Result: unique_id = NULL, name = 'Bob'.
   • Meir (id = 11): Matches id = 11 in EmployeeUNI. Result: unique_id = 2, name = 'Meir'.
   • Winston (id = 90): Matches id = 90 in EmployeeUNI. Result: unique_id = 3, name = 'Winston'.
   • Jonathan (id = 3): Matches id = 3 in EmployeeUNI. Result: unique_id = 1, name = 'Jonathan'.

Final Output Result Set

8. Summary and Key Takeaways

• Transition to Joins: Moving beyond single-table SELECT queries, multi-table queries require relating data using shared keys (Primary Keys and Foreign Keys).
• Primary vs. Foreign Key Relationships: The id column in Employees acts as a Primary Key, while the id in EmployeeUNI acts as a Foreign Key pointing back to the main employee records.
• Choosing LEFT JOIN: Use a LEFT JOIN when you must retain all records from the left-hand table regardless of whether a matching record exists in the right-hand table.
• Handling Missing Matches: Unmatched rows resulting from a LEFT JOIN automatically populate with NULL values in the result set.
• Table Aliasing (AS): Shorten table names (e.g., Employees AS e) to make complex multi-table queries clean, readable, and maintainable.
• Column Aliasing: Use AS column_name in the SELECT clause to guarantee exact output column naming conventions required by interview test runners and client APIs.