Table: Person
LeetCode Solutions+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.
Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.
For SQL users, please note that you are supposed to write a DELETE statement and not a SELECT one.
For Pandas users, please note that you are supposed to modify Person in place.
After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Person table does not matter.
The result format is in the following example.
Person table: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+
+----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
The time complexity is because of the self-join operation in the subquery, which compares each row with every other row to find duplicates.
The space complexity is because of the temporary table created by the subquery to hold the results of the join operation.
The query deletes rows from the Person table where the id is in a subquery that selects the id of duplicate emails. The subquery joins the Person table with itself to find pairs of rows with the same email, and it selects the id of the row with the larger id (the duplicate). The outer query then deletes those duplicate rows, leaving only the row with the smallest id for each unique email.
The time complexity is because of the self-join operation in the query, which compares each row with every other row to find duplicates.
The space complexity is because of the temporary table created by the join operation to hold intermediate results.
This alternative approach uses a DELETE statement with a JOIN to remove duplicate emails. It joins the Person table with itself on the email column, and deletes rows from the second instance (p2) where the id is greater than the corresponding row in the first instance (p1). This effectively removes all duplicate emails except for the one with the smallest id.
| Approach | Rating | Time Complexity | Space Complexity | Advantages | Disadvantages |
|---|---|---|---|---|---|
| Using Subquery | Explicit and easy to understand the logic step by step | Requires a nested subquery which adds complexity | |||
| Using JOIN | Most concise and idiomatic SQL, directly deletes duplicates in a single statement | Same time complexity as the subquery approach due to self-join |
The optimal approach for this problem is Solution 2: Using JOIN. While both solutions have time complexity due to the self-join, the JOIN approach is more concise and idiomatic, directly deleting duplicate rows in a single DELETE ... JOIN statement without needing a nested subquery.
DELETE FROM Person AS pWHERE p.id IN ( SELECT id FROM ( SELECT p2.id FROM PERSON AS p1 JOIN PERSON AS p2 ON p1.email = p2.email WHERE p2.id > p1.id ) AS tmp);DELETE p2 FROM Person AS p1 JOIN Person AS p2 ON p1.email = p2.email WHERE p2.id > p1.id;