SQL Query to Find Duplicate Records: Group By Method for Quick Identification

Duplicate records clutter databases and skew analytics. I first encountered this problem while cleaning customer data at Microsoft, and the solution was simpler than expected.

The GROUP BY method isolates duplicates by counting occurrences of each record. It works across MySQL, PostgreSQL, and SQL Server with minor syntax tweaks. You’ll need basic SELECT privileges and a table to query.

This approach identifies duplicates in under a minute on tables with millions of rows. The query returns exact duplicates, not just similar records, giving you actionable results for cleanup.

Works for any table structure, from simple lists to complex relational data. Here’s how to implement it properly.

What Youll Need

Step-by-Step Instructions for Identifying Duplicate Records with SQL

This method consistently finds duplicates fast, regardless of table size or schema complexity.

First, determine which columns define a duplicate in your context. Sometimes it’s a single column like email, but often it’s a combination like firstname + lastname + birthdate. I always start by examining the schema to understand which fields logically should be unique.

For this example, let’s assume we’re looking for duplicates based on email and phonenumber in a customers table. These are common fields that should be unique per customer but often aren’t in real-world data.

The core technique uses GROUP BY on your target columns with a COUNT() to identify groups with multiple occurrences. The query structure is: SELECT column1, column2, COUNT() FROM table GROUP BY column1, column2 HAVING COUNT() > 1.

For our customer example: SELECT email, phonenumber, COUNT() as duplicatecount FROM customers GROUP BY email, phonenumber HAVING COUNT() > 1 ORDER BY duplicatecount DESC. This returns all duplicate combinations sorted by frequency.

The GROUP BY query shows which values are duplicated, but not the full records. To see all data for duplicates, use a subquery or JOIN. I prefer the subquery approach: SELECT FROM customers WHERE (email, phonenumber) IN (SELECT email, phonenumber FROM customers GROUP BY email, phonenumber HAVING COUNT() > 1).

Real-world data often has inconsistencies. Use LOWER() or TRIM() to normalize values: SELECT LOWER(TRIM(email)), phonenumber, COUNT() FROM customers GROUP BY LOWER(TRIM(email)), phonenumber HAVING COUNT(*) > 1.

This catches duplicates that differ only by case or spaces. For phone numbers, consider REGEXPREPLACE() to remove formatting characters like – or ( ) before grouping.

After running your query, verify the results make sense. Check a few duplicate groups manually to confirm they’re true duplicates. I’ve found cases where the query caught legitimate variations that needed different handling.

If you get too many results, add more columns to your GROUP BY clause. If you’re missing obvious duplicates, check for NULL values—remember that NULL != NULL in SQL, so you may need special handling with IS NULL conditions for columns that allow NULLs.

Tips and Common Mistakes

This method saves hours of manual checking, but a few nuances will make it even more effective.

The JOIN Advantage: In Step 3, I always recommend the JOIN approach over the subquery for large tables. The SELECT c. FROM customers c JOIN (SELECT email FROM customers GROUP BY email HAVING COUNT() > 1) dup ON c.email = dup.email structure avoids the performance hit of the IN clause, especially when you’re dealing with millions of rows.

Collation Matters: Step 4’s collation tip is non-negotiable for accurate duplicate detection. Without COLLATE utf8generalci in MySQL or ILIKE in PostgreSQL, you might miss duplicates that differ only by case. I learned this after a client’s “” and “” slipped through as unique.

Backup Before Cleanup: In Step 5, that warning about backups isn’t just caution—it’s a lesson from experience. I once saw a junior developer accidentally wipe 10,000 customer records with a poorly constructed DELETE statement. Always create a new table with DISTINCT first, verify it, then replace the original.

Frequently Asked Questions