Software Development

# SQL GROUP BY and HAVING Example – Write SQL Query to find Duplicate Emails – LeetCode Solution

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.

## SQL query to find duplicate values in a Column – Solution

Here are three ways to solve this problem in SQL query, first by using group by clause, second by using self-join and then third by using subquery with exists clause.  While I agree that this problem can be solved in a different way, but it is also a perfect example of how you can use the
SQL GROUP BY and HAVING clause.

### 1. Finding Duplicate elements By using GROUP BY

The simplest solution to this problem is by using GROUP BY and HAVING Clause. Use GROUP BY to group the result set on email, this will bring all duplicate email in one group, now if the count for a particular email is greater than 1 it means it is a duplicate email. Here is the SQL query to find duplicate emails :

# Write your MySQL query statement below

 1 `SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email) > ``1`

This is also my accepted answer on LeetCode.

### 2. Finding Duplicate values in a column By using Self Join

By the way, there are a couple of more ways to solve this problem, one is by using Self Join. If you remember, In Self Join we join two instances of the same table to compare one record to another. Now if email from one record in the first instance of the table is equal to the email of another record in the second table it means the email is duplicate. Here is the SQL query using Self Join

# Write your MySQL query statement below

 1 `SELECT DISTINCT a.Email FROM Person a JOIN  Person b ON a.Email = b. Email WHERE a.Id != b.Id`

Remember to use the keyword distinct here because it will print the duplicate email as many times it appears in the table. This is also an accepted solution in Leetcode.

### 3. Finding duplicate emails By using Sub-query with EXISTS:

You can even solve this problem using a correlated subquery. In a correlated subquery, the inner query is executed for each record in the outer query. So one email is compared to the rest of the email in the same table using a correlated subquery. Here is the solution query :

 12345678 `SELECT DISTINCT p1.Email``FROM Person p1``WHERE EXISTS(``    ``SELECT *``    ``FROM Person p2``    ``WHERE p2.Email = p1.Email``    ``AND p2.Id != p1.Id``)`

If you still need more guidance then this Julia Evans zine is also a great way to understand how to find duplicate elements in a table using GROUP BY  with HAVING clause

That’s all about how to find duplicate emails in SQL using the GROUP BY and HAVING clause. I have also shown you how you can solve this problem using Self-join and a subquery with the EXISTS clause as well. Once you get familiar with the pattern you can solve many such problems. If you want to learn more check out the following resources.

 Published on Java Code Geeks with permission by Javin Paul, partner at our JCG program. See the original article here: SQL GROUP BY and HAVING Example – Write SQL Query to find Duplicate Emails – LeetCode SolutionOpinions expressed by Java Code Geeks contributors are their own.

### Javin Paul

I have been working in Java, FIX Tutorial and Tibco RV messaging technology from past 7 years. I am interested in writing and meeting people, reading and learning about new subjects.
Subscribe
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1 Comment
Inline Feedbacks