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?. Is the same customer in the system twice? Duplicates break counts, send two emails to one person, and confuse reports. In SQL, group by and having find them fast. Let's look.
- The customers table. We'll check the customers table. An email should belong to one customer only.
Count each email
- Count each value. Group by email, and count star counts the customers with each email. Call the count copies. Thirty eight rows, one per email. Most say one copy. Reading them all would take a while.
SELECT email, COUNT(*) AS copies FROM customers GROUP BY email;
HAVING
- HAVING. Having filters the groups after counting. Having count star greater than one keeps only emails used more than once. Two rows. Liam Doyle's email appears twice. The other row is empty: two customers have no email at all.
HAVING COUNT(*) > 1;
Skip empty values
- Skip empties. Missing emails are a different problem. Add where email is not null, before the group by. One real duplicate left.
WHERE email IS NOT NULL GROUP BY
Two columns
- Same name. Duplicates can hide in other columns too. Group by first name and last name together, to find people entered twice. The same person again. Liam Doyle, two times.
SELECT first_name, last_name, COUNT(*) AS copies FROM customers GROUP BY first_name, last_name HAVING COUNT(*) > 1;
Full records
- See the records. Now show the full records. Select star where the email is in the list of duplicate emails. The list in brackets is the query we just built. Customer twelve and customer thirty eight. Same name, same email, same city, but two different I Ds. That's one to raise with the data owner.
SELECT * FROM customers WHERE email IN ( SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1);
Recap
- Find duplicates. So, group by the column that should be unique, and keep groups having count star greater than one. Leave out empty values, then pull the full records to check them.
