182. 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.


b'
\n\n

Solution

\n
\n

Approach I: Using GROUP BY and a temporary table [Accepted]

\n

Algorithm

\n

Duplicated emails existed more than one time. To count the times each email exists, we can use the following code.

\n
select Email, count(Email) as num\nfrom Person\ngroup by Email;\n
\n
| Email   | num |\n|---------|-----|\n| [email protected] | 2   |\n| [email protected] | 1   |\n
\n

Taking this as a temporary table, we can get a solution as below.

\n
select Email from\n(\n  select Email, count(Email) as num\n  from Person\n  group by Email\n) as statistic\nwhere num > 1\n;\n
\n

Approach II: Using GROUP BY and HAVING condition [Accepted]

\n

A more common used way to add a condition to a GROUP BY is to use the HAVING clause, which is much simpler and more efficient.

\n

So we can rewrite the above solution to this one.

\n

MySQL

\n
select Email\nfrom Person\ngroup by Email\nhaving count(Email) > 1;\n
\n
'