Question

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.

SQL Schema

Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (Id, Email) values ('1', '[email protected]')
insert into Person (Id, Email) values ('2', '[email protected]')
insert into Person (Id, Email) values ('3', '[email protected]')

My Interesting Code

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

My Perspective

This question is extremely simple. Firstly, you can use “group by” to combine the same “Email”. Then, you can use “having count” function to select the elements which “Email” addresss appreas at least twice.

More importantly, I have a problem when addressing this question, due to duplicate space between “count” and “(Email)”.

Also, there are two useful links, and I think they can help you. (chinese version)