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

Javin PaulApril 9th, 2020Last Updated: April 8th, 2020

1 783 3 minutes read

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

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 :

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.

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.