[LeetCode] 182. Duplicate Emails

Posted on | In

Problem

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

1
2
3
4
5
6
7
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

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

1
2
3
4
5
+---------+
| Email   |
+---------+
| a@b.com |
+---------+

Note: All emails are in lowercase.

Explanation 1

  1. To return duplicated emails, it means we compare emails that are the same. When comparing and returning the common attributes, we can use INNER JOIN two tables.

  2. The join condition is first table’s email equal to second table’s email, where first table’s id not equal to second table’s id.

Solution 1

1
2
3
4
5
# Write your MySQL query statement below
SELECT DISTINCT p1.Email
FROM Person as p1
INNER JOIN Person as p2
ON p1.Email = p2.Email AND p1.Id != p2.Id;

Explanation 2

  1. We can also use GROUP BY the emails and HAVING the count of email greater than 1.

Solution 2

1
2
3
4
5
# Write your MySQL query statement below
SELECT Email
FROM Person
GROUP BY Email
HAVING Count(Email) > 1;