Finding duplicate records is one of the most common tasks a database administrator or developer faces. Think about it: whether you are cleaning up a messy dataset, preparing for a migration, or enforcing data integrity constraints, knowing how to select duplicate records in SQL is a fundamental skill. And the approach varies slightly depending on the specific database engine—such as MySQL, PostgreSQL, SQL Server, or Oracle—but the core logic remains consistent: identify rows where specific column values appear more than once. This guide provides a comprehensive walkthrough of the standard methods, advanced techniques, and performance considerations for isolating duplicate data effectively Worth keeping that in mind..
Understanding What Constitutes a Duplicate
Before writing a query, you must define what "duplicate" means in your specific context. There are generally two scenarios:
- Complete Row Duplicates: Every single column in one row matches every column in another row exactly.
- Partial (Key) Duplicates: Specific columns—often a business key like
email,username, or a combination offirst_nameandlast_name—match, while other columns (like an auto-incrementidorcreated_attimestamp) differ.
Most real-world scenarios involve the second case. You rarely search for rows where the primary key is duplicated (since PKs enforce uniqueness), but rather where a candidate key or business logic field violates uniqueness.
The Standard Approach: GROUP BY and HAVING
The most universal, ANSI-SQL compliant method for finding duplicates uses the GROUP BY clause combined with HAVING. This works on virtually every relational database system.
Basic Syntax
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
Practical Example
Imagine a customers table where email should be unique, but you suspect data entry errors created duplicates.
SELECT email, COUNT(*) as duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;
How it works:
GROUP BY emailaggregates all rows sharing the same email address into a single bucket.COUNT(*)calculates how many rows fall into each bucket.HAVING COUNT(*) > 1filters out the buckets containing only a single record, leaving only the duplicated emails.ORDER BY duplicate_count DESCputs the worst offenders at the top for immediate attention.
Finding Duplicates Across Multiple Columns
Often, uniqueness is defined by a composite key. Here's one way to look at it: a table tracking class enrollment might require a unique combination of student_id and class_id.
SELECT student_id, class_id, COUNT(*)
FROM enrollments
GROUP BY student_id, class_id
HAVING COUNT(*) > 1;
This query returns the specific combinations that violate the business rule, allowing you to pinpoint exactly which students are enrolled in the same class twice And that's really what it comes down to. Practical, not theoretical..
Retrieving the Full Duplicate Rows
The GROUP BY method is excellent for identifying which values are duplicated, but it only returns the grouped columns and the count. It does not return the other columns (like id, phone, address) for the specific duplicate rows. To see the actual rows, you need to join the result back to the original table or use a subquery.
Method 1: Using a Subquery (IN Clause)
SELECT *
FROM customers
WHERE email IN (
SELECT email
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
)
ORDER BY email;
This selects every column for every row where the email appears in the "duplicates list." It is readable and performs well on small to medium datasets It's one of those things that adds up. Simple as that..
Method 2: Using an INNER JOIN
For larger datasets, an INNER JOIN against a derived table (the aggregate query) is often more performant because the optimizer can use better join algorithms (like Hash Match or Merge Join) rather than executing a subquery for every row.
SELECT c.*
FROM customers c
INNER JOIN (
SELECT email
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
) dup ON c.email = dup.email
ORDER BY c.email;
Both methods achieve the same result: a complete list of every row participating in a duplication conflict, sorted logically so you can visually inspect the differences (e.Day to day, g. , different phone numbers for the same email) That's the whole idea..
The Modern Approach: Window Functions (ROW_NUMBER)
If you are using a modern database engine (PostgreSQL, SQL Server, Oracle, MySQL 8.In real terms, 0+, SQLite), Window Functions provide the most powerful and flexible way to handle duplicates. They allow you to assign a sequential number to each row within a partition (group) without collapsing the result set Easy to understand, harder to ignore..
Syntax using ROW_NUMBER()
WITH RankedCustomers AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) as row_num
FROM customers
)
SELECT *
FROM RankedCustomers
WHERE row_num > 1;
Why this is superior:
- Identifies "Original" vs. "Duplicate": By adding
ORDER BY created_at(orid) inside theOVERclause, you explicitly define which row is the "keeper" (row_num = 1) and which are the extras (row_num > 1). - Access to All Columns: You get the full row data immediately—no joins or subqueries required.
- Flexible Filtering: You can easily select only the duplicates (
row_num > 1), only the originals (row_num = 1), or even the nth occurrence. - Deletion Ready: This CTE (Common Table Expression) structure is the perfect precursor to a
DELETEstatement. You can simply changeSELECT *toDELETE FROM customers WHERE id IN (SELECT id FROM RankedCustomers WHERE row_num > 1).
RANK() vs DENSE_RANK() vs ROW_NUMBER()
- ROW_NUMBER(): Assigns 1, 2, 3, 4... strictly sequentially. Best for deleting duplicates because every row gets a unique ID within the group.
- RANK(): Assigns 1, 2, 2, 4... (skips numbers after ties). Useful for reporting ties but risky for deletion logic.
- DENSE_RANK(): Assigns 1, 2, 2, 3... (no gaps). Similar to RANK but without gaps.
For the specific task of selecting duplicate records in SQL for cleanup, ROW_NUMBER() is almost always the correct choice Took long enough..
Database-Specific Shortcuts
While the standard SQL above works everywhere, some dialects offer syntactic sugar.
MySQL (Pre-8.0) / MariaDB
Older MySQL versions did not support Window Functions. The GROUP BY / JOIN method was standard. On the flip side, MySQL allows non-aggregated columns in SELECT with GROUP BY (unless ONLY_FULL_GROUP_BY mode is enabled), which can be dangerous but occasionally convenient for quick checks:
-- Warning: Non-deterministic results for non-grouped columns
SELECT *, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
Use this only for quick ad-hoc investigation, never for production logic.
PostgreSQL: DISTINCT ON
PostgreSQL offers a unique, highly readable clause DISTINCT ON to keep the "first" row per group And that's really what it comes down to..
-- Selects the first row per email (ordered by id), effectively showing originals
SELECT DISTINCT ON (email) *
FROM customers
ORDER BY email, id;
-- To find duplicates, you typically still use ROW_NUMBER or EXCEPT logic.
SQL Server: TOP with TIES
Useful for sampling, but less
useful for deduplication. The ROW_NUMBER() approach remains the cleanest path for cleanup on SQL Server. That said, TOP (n) WITH TIES shines when you want to keep all tied rows rather than arbitrarily picking one:
-- Returns every row that ties on the highest created_at value
SELECT TOP (5) WITH TIES *
FROM customers
ORDER BY created_at DESC;
Oracle: ROWID for Deletion
Oracle exposes a hidden pseudo-column ROWID that uniquely identifies every physical row. It's invaluable for deduplication because you can delete duplicates without needing a primary key:
DELETE FROM customers c
WHERE c.ROWID NOT IN (
SELECT MIN(d.ROWID)
FROM customers d
GROUP BY d.email
);
SQLite: rowid
SQLite behaves similarly to Oracle—every table (unless declared WITHOUT ROWID) has an implicit rowid you can lean on for the same delete pattern Which is the point..
Practical Best Practices
Before you run any cleanup, keep these guidelines in mind:
- Always back up first. Duplicate removal is destructive. Take a snapshot or run the logic inside a transaction you can roll back.
- Preview before deleting. Run the
SELECTversion of your query and eyeball the rows. Confirm the "keeper" logic (ORDER BY) matches your intent. - Beware of NULLs. In most engines,
NULLvalues are not considered equal to each other underGROUP BY... actually, they are grouped together byGROUP BY, but they are not equal underJOINconditions. This inconsistency trips people up. Decide explicitly how you want to treat NULL emails before grouping. - Add a unique constraint afterward. Once duplicates are gone, prevent their return with a
UNIQUEindex on the deduplication key (e.g.,email). This turns a one-time cleanup into a permanent guarantee. - Watch for case sensitivity.
John@Example.comandjohn@example.commay be the same person. Normalize withLOWER(email)in your grouping logic if your data allows mixed casing.
Conclusion
Finding and removing duplicate records is one of those tasks that looks trivial until you're staring at a table with millions of rows and no clean primary key. The good news is that modern SQL gives you a reliable, portable tool for the job: the ROW_NUMBER() window function. By partitioning on your deduplication key and ordering by a tiebreaker like created_at or id, you gain precise control over which row survives and which rows get flagged for removal Small thing, real impact. That's the whole idea..
The GROUP BY / HAVING COUNT(*) > 1 pattern is still useful for a quick "do I even have a problem?" check, and each database dialect offers its own conveniences—DISTINCT ON in PostgreSQL, ROWID in Oracle, WITH TIES in SQL Server. But when it comes to production-grade cleanup, reach for ROW_NUMBER() every time Small thing, real impact..
The real win, though, isn't the deletion—it's what comes after. A duplicate-free table protected by a UNIQUE constraint is a table that stays clean. Treat deduplication not as a chore but as the first step toward better data hygiene, and your future self (and your queries) will thank you.