This website contains ALL LeetCode **Premium** problems for
**FREE!!**.

All leaked interview problems are collected from Internet.

All leaked interview problems are collected from Internet.

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

.

+----+---------+ | 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:

+---------+ | Email | +---------+ | a@b.com | +---------+

**Note**: All emails are in lowercase.

b'

\n## Solution

\n

\n#### Approach I: Using

\n\n\n\n#### Approach II: Using

\n\n

'
\n\n

\n\n

`GROUP BY`

and a temporary table [Accepted]**Algorithm**

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

\nselect Email, count(Email) as num\nfrom Person\ngroup by Email;\n

| Email | num |\n|---------|-----|\n| a@b.com | 2 |\n| c@d.com | 1 |\n

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

\nselect 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

`GROUP BY`

and `HAVING`

condition [Accepted]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.

So we can rewrite the above solution to this one.

\n**MySQL**

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