Find Duplicate Emails

We can use the below keywords to find the duplicate values of a table column.

  • GROUP BY, which groups the rows with the same column value included in the GROUP BY keyword
  • COUNT, which returns the count of rows

Let's assume a table with the below table definition and data, where we need to find the duplicate emails.

id   name       email
===========================
1    Arun       arun@example.com
2    Kiran      kiran@example.com
3    James      arun@example.com
4    Sammy      sammy@example.com
5    Martin     martin@example.com

SQL Query Using GROUP BY and COUNT

SELECT email FROM employees GROUP BY email HAVING COUNT(email) > 1;

This query returns only the emails that exist more than once in the table, which means duplicate emails.

  • Firstly, the GROUP BY condition groups all the rows based on email, and a count is generated for each email based on the number of rows grouped for that email.
  • Then, the query returns only the emails that have a count of more than one, which means duplicate emails.

Overall

We now know the SQL Query to find a list of duplicate values from a specific table column.

Related Links