SQL Coding Exercise – Duplicate Emails

The SQL coding exercise is from oj-online judge and you could submit your solution to this URL: https://oj.leetcode.com/problems/duplicate-emails/

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | [email protected] |
| 2  | [email protected] |
| 3  | [email protected] |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| [email protected] |
+---------+

All emails will be assumed in lower-case letters so you don’t need to worry about comparing mixed-cases emails (upper and lower cases)

The easy-to-understand solution would be to group by Email column and count the rows where email has appeared more than once.

# 973 ms
select distinct `Email` from `Person` group by `Email` Having Count(`Email`) > 1;

We can also use self-join approach:

# 987 ms
SELECT DISTINCT a.Email
FROM Person a JOIN Person b
ON (a.Email = b.Email)
WHERE a.Id <> b.Id

The alternative solution is to use subquery EXISTS:

# 1287 ms
SELECT DISTINCT a.Email
FROM Person a
WHERE EXISTS(
    SELECT 1
    FROM Person b
    WHERE a.Email = b.Email
    LIMIT 1,1
)

We can also left-join.

# 1157 ms
SELECT DISTINCT a.Email FROM Person a
LEFT JOIN (SELECT Id, Email from Person GROUP BY Email) b
ON (a.email = b.email) AND (a.Id = b.Id)
WHERE b.Email IS NULL

–EOF (The Ultimate Computing & Technology Blog) —