{x}
blog image

Replace Employee ID With The Unique Identifier

Table: Employees

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table contains the id and the name of an employee in a company.

 

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 user, If a user does not have a unique ID replace just show null.

Return the result table in any order.

The result format is in the following example.

 

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, We will show null instead.
The unique ID of Meir is 2.
The unique ID of Winston is 3.
The unique ID of Jonathan is 1.

Solution Explanation for Replace Employee ID With The Unique Identifier

This problem requires querying two tables, Employees and EmployeeUNI, to generate a result showing employee names and their corresponding unique IDs. If an employee doesn't have a unique ID in EmployeeUNI, null should be displayed.

Approach

The most efficient way to solve this is using a LEFT JOIN. A LEFT JOIN ensures that all rows from the left table (Employees in this case) are included in the result. If a matching row exists in the right table (EmployeeUNI), the corresponding columns from the right table are included; otherwise, NULL values are used for the columns from the right table.

SQL Query (MySQL)

SELECT unique_id, name
FROM
    Employees
    LEFT JOIN EmployeeUNI USING (id);

This query performs a LEFT JOIN between the Employees and EmployeeUNI tables using the id column as the join key. The USING (id) clause is a shorthand for specifying the join condition; it's equivalent to ON Employees.id = EmployeeUNI.id. The result set includes the unique_id from EmployeeUNI and the name from Employees. If an employee's id is not found in EmployeeUNI, the unique_id will be NULL.

Time and Space Complexity Analysis

Time Complexity: The time complexity of this SQL query is dominated by the LEFT JOIN operation. In the worst case, the join will have to compare each row in Employees with each row in EmployeeUNI. However, the database engine is highly optimized to perform joins efficiently using indexing techniques (if indexes are present on the id column in both tables). Therefore, the actual time complexity is dependent on the database engine's implementation and the size of the tables, but it's generally considered to be close to O(m + n) or even better with proper indexing, where 'm' and 'n' are the numbers of rows in Employees and EmployeeUNI respectively (linear time complexity in the ideal case with indexes).

Space Complexity: The space complexity is determined by the size of the result set, which is at most the size of the Employees table (since all rows from Employees are included). Therefore, the space complexity is O(m) in the worst case, where 'm' is the number of rows in the Employees table. The actual space used can vary depending on the database system's internal memory management.

Note: The exact time complexity can be influenced by factors like the presence of indexes on the id column in both tables, the database engine's query optimizer, and the size of the tables. A well-indexed database will significantly improve the query's performance.