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.
This approach involves using SQL join operations to combine data from the Employees and EmployeeUNI tables to generate the desired result. Specifically, a LEFT JOIN can be used here to ensure that all employees are included in the result, even if they do not have a corresponding unique ID.
In this SQL query, we perform a LEFT JOIN between the Employees table and the EmployeeUNI table on the matching id field. This ensures that all records from Employees are retained in the result, and the unique_id from EmployeeUNI is retrieved where available. If there is no match, unique_id is null.
Time Complexity: O(n), where n is the number of rows in the Employees table.
Space Complexity: O(m + n), where m and n are the number of rows in the Employees and EmployeeUNI tables, respectively.
Another approach involves simulating SQL-like operations using constructs provided by programming languages. This essentially involves iterating over both tables and manually matching entries based on the id column.
In this Python example, we first create a dictionary id_to_unique_id to map each id to its unique_id from the employee_uni list. We then iterate over the employees list, checking for each employee's id in the dictionary. If it exists, we extract the unique_id; otherwise, we use None. Finally, we build a list of dictionaries to hold the result.
Time Complexity: O(n + m), where n and m are lengths of employees and employee_uni, respectively.
Space Complexity: O(n + m), as we store all employees and the mapping dictionary in memory.
This approach involves using an INNER JOIN to combine the Employees and EmployeeUNI tables based on the employee ID. However, INNER JOIN will only include rows that have matches in both tables. To account for employees without a unique ID, we can consider using an OUTER JOIN as described in the next approach.
This SQL statement selects the employee ID, name, and unique ID for employees that have a corresponding entry in the EmployeeUNI table.
The time complexity is O(n) because the SQL engine needs to scan both tables. The space complexity depends on the SQL engine's implementation.
The more suitable approach is the LEFT JOIN because it allows for including all records from the Employees table, and the matching records from the EmployeeUNI table. If there is no match, the result is NULL which solves the requirement of showing 'null' for employees without a unique ID.
This SQL statement joins the Employees table with EmployeeUNI using a LEFT JOIN, ensuring all employees are listed with their unique ID if available, or NULL otherwise.
The time complexity for a LEFT JOIN is generally O(n + m) where n and m are the number of rows in the Employees and EmployeeUNI tables respectively. Space complexity also depends on implementation details in the SQL engine.
| Approach | Complexity |
|---|---|
| SQL Join Operation | Time Complexity: O(n), where n is the number of rows in the |
| Nested Loop Simulation Using Language Constructs | Time Complexity: O(n + m), where n and m are lengths of |
| Inner Join Approach | The time complexity is O(n) because the SQL engine needs to scan both tables. The space complexity depends on the SQL engine's implementation. |
| Left Join Approach | The time complexity for a LEFT JOIN is generally O(n + m) where n and m are the number of rows in the Employees and EmployeeUNI tables respectively. Space complexity also depends on implementation details in the SQL engine. |
6. Replace Employee ID With The Unique Identifier | SQL Leetcode • Start Practicing • 10,406 views views
Watch 9 more video solutions →Practice Replace Employee ID With The Unique Identifier with our built-in code editor and test cases.
Practice on FleetCode