Find duplicates using SQL

To find duplicates in one or more columns of a table in SQL, use the GROUP BY clause with the HAVING clause.

Consider a table named employees with columns id, name, and email.

Now find duplicate email addresses.

Use the following SQL query:

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

This query will count the number of rows containing each email address and group the rows by email address. The HAVING clause then narrows the results to just display duplicate email addresses, or email addresses with more than one row. Include those columns in the GROUP BY clause in order to discover duplication across several columns. For example, to find duplicate names and email addresses.

Use the following SQL query:

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

This query groups the rows by both name and email address and filters the results to only show rows with duplicate combinations of name and email.