How to Find Duplicate Records in SQL (GROUP BY + HAVING)
GROUP BY with HAVING COUNT(*) > 1 finds values that appear more than once. Find a repeated customer email, leave out empty values, check names, and list the full duplicate records, on a sample online shop database.
Step by step
Duplicates
- Same customer twice?. Duplicate records break counts, send 2 emails to 1 person and confuse reports. In SQL, GROUP BY and HAVING find them quickly.
- The customers table. We check the customers table. Each email should belong to only 1 customer.
Count each email
- Count each value. GROUP BY email puts all rows with the same email into one group. COUNT(*) counts the rows in each group, and AS copies names that count. The result has 38 rows, one per email, and most show 1 copy. Reading every row to spot the duplicates would be slow.
SELECT email, COUNT(*) AS copies FROM customers GROUP BY email;
HAVING
- HAVING. HAVING filters the groups after they are counted. Replace the final semicolon with HAVING COUNT(*) > 1; to keep only emails used more than once. Now there are 2 rows. Liam Doyle's email appears twice. The other row has an empty (NULL) email, because 2 customers have no email at all and GROUP BY puts them in one group.
HAVING COUNT(*) > 1;
Skip empty values
- Skip empties. Missing emails are a different problem from duplicates. Add WHERE email IS NOT NULL before the GROUP BY line. WHERE removes rows before they are grouped. The query now returns 1 row: the one real duplicate email.
WHERE email IS NOT NULL GROUP BY
Two columns
- Same name. Duplicates can hide in other columns too. Clear the editor and group by first_name and last_name together, with the same HAVING COUNT(*) > 1, to find people entered twice. It returns 1 row: Liam Doyle, with 2 copies. It's the same person the email check found.
SELECT first_name, last_name, COUNT(*) AS copies FROM customers GROUP BY first_name, last_name HAVING COUNT(*) > 1;
Full records
- See the records. To see the full records, SELECT * from customers where the email is IN a list. The list in brackets is the duplicate-email query from before, used as a subquery. It returns customer 12 and customer 38: same name, same email, same city, but 2 different IDs. The empty-email group doesn't match, because NULL never equals anything. Raise this pair with the data owner.
SELECT * FROM customers WHERE email IN ( SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1);
Recap
- Find duplicates. To sum up: GROUP BY the column that should be unique, keep the groups HAVING COUNT(*) > 1, leave out empty values with WHERE ... IS NOT NULL, then pull the full records to check them.
Common mistakes
- Putting COUNT(*) > 1 in WHERE. WHERE runs before the rows are grouped, so it can't use COUNT and the query gives an error. Filter on counts with HAVING, after GROUP BY.
- Including the ID in the GROUP BY. Every row has its own ID, so every group has 1 row and no duplicates show up. Group only by the columns that should match, like email or first_name and last_name.
- Treating empty values as duplicates. GROUP BY puts all NULL values in one group, so rows with no email look like a duplicate. Add WHERE email IS NOT NULL and handle missing values as a separate issue.
- Missing near-duplicates. An extra space or different capital letters can stop 2 emails from grouping together, depending on the database's settings. Group by LOWER(TRIM(email)) to catch these too.
When a business analyst uses this
Before a data migration go-live, a business analyst runs a duplicate check on the customer table and finds customers 12 and 38 sharing one email. They log it as an issue in the RAID log with both IDs and ask the data owner which record to keep before sign-off.
Questions people ask
Does this work in SQL Server?
Yes. GROUP BY with HAVING COUNT(*) > 1 is standard SQL, so the same queries work in SQL Server, MySQL, PostgreSQL, SQLite and Oracle.
How do I find duplicate records when each row has a primary key?
Leave the primary key out of the GROUP BY, because it's different on every row. Group by the columns that should be unique, like email, then use the WHERE email IN (...) query to see each copy's ID.
Can I find duplicates with ROW_NUMBER instead?
Yes, in databases with window functions, such as SQL Server, PostgreSQL and MySQL 8 or later. ROW_NUMBER() OVER (PARTITION BY email ORDER BY the ID) numbers each copy, and any row numbered 2 or more is an extra copy.
