It’s an easy problem from SQL 50 with the description being:
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 is a problem where you have to join two different tables, hence it makes since to use the SQL JOIN clause.
Since if there is a requirement that if the UNIQUE ID user doesn’t exist to show null then it makes sense to make a JOIN that includes the first table and ignores the second if the joined condition doesn’t exist (which will cause the value to be null).
Here above there is an example of a left join where two tables were joined by an common column and with that where able to identify the similar ones.
Since there was no D on the second one the result would be NULL because C exists only on the first one.
If we use that same idea for this problem the SQL Query would be:
SELECT eu.unique_id, e.name
FROM Employees e
LEFT JOIN EmployeeUNI eu
ON e.id = eu.id;
Runtime: 2841 ms, faster than 44.54% of MySQL online submissions for Replace Employee ID With The Unique Identifier.
Memory Usage: 0B, less than 100.00% of MySQL online submissions for Replace Employee ID With The Unique Identifier.
That’s it!
If there is anything thing else to discuss feel free to drop a comment, if I missed anything let me know so I can update accordingly.
Until next post! :)
Top comments (0)