Source

• leetcode: 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] |
+---------+
Note: All emails are in lowercase.

MySQL

-- select distinct p1.Email
-- from Person p1
-- join Person p2
-- on p1.Email=p2.Email
-- where p1.Id<>p2.Id

select Email 
from Person 
group by Email 
having count(*) > 1