Removing duplicate rows in SQL is a common requirement when cleaning data or ensuring accurate reporting. Developers and analysts often need techniques that preserve one instance of each record while discarding extras efficiently.
This guide walks through practical methods, edge cases, and performance considerations for excluding duplicates across different database platforms. The following reference materials will help you choose the right approach for your schema and query patterns.
| Technique | Use Case | Performance | Database Support |
|---|---|---|---|
| DISTINCT | Simple unique value lists | Low overhead for small sets | All SQL databases |
| GROUP BY | Aggregation with uniqueness | Good with indexed group keys | All SQL databases |
| ROW_NUMBER() PARTITION BY | Keep one row per group based on order | Moderate, benefits from partitioning indexes | SQL Server, PostgreSQL, Oracle, MySQL 8+, SQLite |
| CTE with ROW_NUMBER delete | In-place de-duplication in a table | Higher I/O on large tables; index friendly | SQL Server, PostgreSQL, Oracle, MySQL 8+ |
| DELETE USING self-join | Remove duplicates by key without window functions | Can be fast with proper join indexes | MySQL, MariaDB, older SQL Server |
Handling Duplicates with DISTINCT and GROUP BY
The simplest way to exclude duplicates in SQL is to use DISTINCT on selected columns. This approach projects only unique combinations and works well for reporting or lookup queries.
GROUP BY provides similar deduplication powers while enabling aggregation. Pair it with aggregate functions to summarize rows per unique key and avoid returning repeated values.
Removing Duplicates with Window Functions
Window functions like ROW_NUMBER allow fine-grained control when you need to keep one representative from each duplicate set. By partitioning on business keys and ordering by a timestamp or ID, you can consistently select the desired row.
Use a common table expression or derived table to filter on row number equals one. This pattern is helpful when duplicates must be removed from a target table through deletion or archival.
De-duplicating Tables with CTE and DELETE
Many production tables contain hidden duplicates that violate intended uniqueness. A CTE combined with ROW_NUMBER and DELETE can surgically remove extra copies while preserving one valid row per group.
Always test such delete logic in a non-production environment and ensure you have a backup or transaction rollback plan. Indexes on the partition and ordering columns dramatically improve performance and reduce lock contention.
Performance and Indexing Considerations
Excluding duplicates efficiently requires strategic indexing on the columns used for partitioning and ordering. Without suitable indexes, window functions and self-joins may trigger expensive sort operations and table scans.
Consider query plans, intermediate result sizes, and isolation levels. For very large tables, batch processing or archiving older data can keep de-duplication operations responsive and maintainable.
Key Takeaways for SQL Duplicate Management
- Choose DISTINCT or GROUP BY for lightweight unique projections
- Use ROW_NUMBER PARTITION BY for controlled row selection within duplicate groups
- Back up data and test DELETE strategies in a safe environment first
- Index partition and order columns to maintain performance
- Apply constraints and proactive validation to prevent future duplicates
FAQ
Reader questions
How do I remove duplicates from a table while keeping one row per group?
Use a CTE that assigns ROW_NUMBER() OVER (PARTITION BY key_columns ORDER BY keep_column), then DELETE FROM the table WHERE the primary key matches rows with row number greater than one.
Can DISTINCT be used to exclude duplicates in an INSERT operation?
Yes, wrap the source query with SELECT DISTINCT and insert into the target table. Ensure target constraints align to avoid unique violations from other sources.
What is the fastest way to exclude duplicates on large tables?
Create appropriate indexes on partition and order columns, then use a set-based approach such as DELETE with a self-join or a ROW_NUMBER CTE. Avoid row-by-row processing for better throughput.
How can I prevent duplicates when loading new data into a table?
Define unique constraints or indexes on the relevant columns, and use INSERT IGNORE, ON CONFLICT DO NOTHING, or MERGE statements depending on your database platform.