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  | |
| 2  | |
| 3  | |

For example, your query should return the following for the above table:

| Email   |
| |

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

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


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

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 :

FROM Person p1
    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.

Thanks for reading this article, if you like this SQL article, then please share with your friends and colleagues. If you have any questions or feedback, then please drop a note.

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 Solution

Opinions 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.
Notify of

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

1 Comment
Newest Most Voted
Inline Feedbacks
View all comments
John Smith
John Smith
3 years ago

Thanks for the helpful article! I’m just going to open my own company and collect information that may be useful to me. I noticed that large companies pay a lot of attention to the structure of the email. Any tips in this area? I know that many people use to make a correct and interesting signature, how do you feel about that?

Back to top button