How to Remove Duplicates in SQL? (3 Easy Methods)

Duplicate data can clutter your database, lead to inaccurate reporting, and impact query performance. Knowing how to remove duplicates in SQL is essential for keeping your datasets clean and efficient.

In this guide, we’ll cover the best practices and various methods to eliminate duplicate entries from your SQL tables. You’ll learn the steps to identify and delete duplicates in SQL, and we’ll go over practical SQL commands that will make this process straightforward.

Why Removing Duplicates is Important

Duplicate rows in a database table can cause inaccurate analysis results and inflate storage requirements. When databases are used for analytics, data warehouses, or reporting, cleaning duplicates ensures that you’re working with valid data. By removing duplicate records, you enhance the reliability of your data, improve performance, and prevent costly mistakes in data interpretation.

Identifying Duplicate Rows in SQL

Before you can remove duplicates, you first need to identify them. In SQL, you can use specific queries to find duplicate rows in your tables. Identifying duplicates helps confirm the presence and nature of duplicate entries, allowing for precise deletion.

Using the GROUP BY and HAVING Clauses

The GROUP BY clause groups rows based on column values, while the HAVING clause filters groups based on conditions. Together, they allow you to detect duplicate rows.

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

In this query:

• column_name is the column to check for duplicates.
• table_name is the name of your table.
• COUNT(*) > 1 filters for rows with duplicate values in the specified column.

Finding Multiple Duplicate Columns

If duplicates are defined by a combination of columns, list them in the GROUP BY clause.

SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

This query will identify rows where column1 and column2 have the same values in more than one row, highlighting all such duplicates.

Methods to Remove Duplicates in SQL

After identifying duplicates, the next step is deleting duplicate rows. SQL offers several ways to do this, depending on your database setup and requirements.

Method 1: Using the ROW_NUMBER() Window Function

The ROW_NUMBER() function assigns a unique number to each row based on a specified ordering. You can then use it to delete duplicate rows while keeping one instance intact.

WITH duplicates AS (
    SELECT column_name, 
           ROW_NUMBER() OVER(PARTITION BY column_name ORDER BY id_column) AS row_num
    FROM table_name
)
DELETE FROM duplicates
WHERE row_num > 1;

• PARTITION BY groups rows based on the column_name for which duplicates are being removed.
• ORDER BY id_column determines which row will be retained (typically the row with the lowest ID).
• row_num > 1 filters for duplicate entries.

Note: This method retains only the row with the smallest id_column value, which is useful when ordering is necessary.

Method 2: Using Temporary Tables

Temporary tables can also help when removing duplicates in SQL. This method involves creating a new table, inserting unique records, and then replacing the original table.

CREATE TABLE temp_table AS
SELECT DISTINCT *
FROM table_name;

DROP TABLE table_name;

ALTER TABLE temp_table RENAME TO table_name;

• DISTINCT ensures only unique rows are copied to the temporary table.
• DROP TABLE removes the original table, and ALTER TABLE renames the temporary table to replace it.

Method 3: Using the DELETE Statement with Subquery

The DELETE statement can remove duplicates using a subquery, often in combination with MIN() or MAX().

DELETE FROM table_name
WHERE id_column NOT IN (
    SELECT MIN(id_column)
    FROM table_name
    GROUP BY column_name
);

This query keeps only the row with the smallest id_column for each duplicate group. It’s ideal if your table contains a unique identifier like id_column.

Practical Examples of Removing Duplicates in SQL

Let’s look at some practical examples to see these methods in action.

Example 1: Removing Duplicates Based on a Single Column

Imagine you have a table named employees with duplicate entries in the email column.

DELETE FROM employees
WHERE email NOT IN (
    SELECT MIN(id)
    FROM employees
    GROUP BY email
);

This code will keep the first instance of each email and delete the rest.

Example 2: Removing Duplicates Based on Multiple Columns

If duplicates are based on more than one column, such as first_name and last_name, you can modify the query to accommodate multiple columns.

DELETE FROM employees
WHERE id NOT IN (
    SELECT MIN(id)
    FROM employees
    GROUP BY first_name, last_name
);

By grouping both first_name and last_name, this example deletes duplicate entries for employees with the same name.

Understanding SQL Functions to Handle Duplicates

SQL provides several functions that simplify the duplicate-removal process. Here are a few essential functions you’ll find useful.

1. DISTINCT

The DISTINCT keyword is used in SELECT queries to return only unique records. While it doesn’t delete duplicates, it helps isolate unique rows.

SELECT DISTINCT column_name FROM table_name;

This will retrieve unique values in column_name.

2. ROW_NUMBER()

As mentioned, ROW_NUMBER() is highly effective for identifying duplicates within each partition. It’s a good choice when you want to remove duplicates based on certain ordering criteria.

3. RANK() and DENSE_RANK()

These functions are similar to ROW_NUMBER() but differ in handling duplicate values. RANK() assigns the same rank to identical values but skips subsequent ranks. DENSE_RANK() gives the same rank without skipping numbers.

Handling Duplicates in Different SQL Database Systems

Different SQL systems have slight variations in syntax. Here are some considerations for removing duplicates in various SQL platforms.

Removing Duplicates in MySQL

In MySQL, you can combine DELETE and JOIN to delete duplicates effectively:

DELETE e1 FROM employees e1
JOIN employees e2
WHERE e1.email = e2.email AND e1.id > e2.id;

This removes duplicates based on the email column, keeping the row with the smallest id.

Removing Duplicates in PostgreSQL

PostgreSQL supports ROW_NUMBER() as in the following example:

DELETE FROM employees
WHERE id IN (
    SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) AS row_num
        FROM employees
    ) AS duplicates
    WHERE row_num > 1
);

In this example, the subquery identifies duplicates in the email column, and the outer DELETE query removes them.

Removing Duplicates in SQL Server

SQL Server uses similar syntax with ROW_NUMBER():

WITH duplicates AS (
    SELECT id, ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) AS row_num
    FROM employees
)
DELETE FROM duplicates
WHERE row_num > 1;

This removes duplicate emails, keeping the record with the smallest id.

Best Practices for Managing Duplicates in SQL

• Regular Data Audits: Run periodic checks on your tables to detect duplicates before they impact reporting.
• Unique Constraints: Where possible, add unique constraints to prevent duplicate data from entering tables.
• Indexing: Use indexes on frequently checked columns for better performance.
• Backup Tables: Always back up your tables before running delete operations, especially in production databases.

Sample Table for Reference

Here’s an example table schema for understanding these duplicate-removal techniques.

Using the queries provided in this guide, you can identify and delete duplicate rows in similar tables effectively.

Final Thoughts

Removing duplicates in SQL is an essential skill for maintaining clean, efficient databases. Using methods like ROW_NUMBER(), temporary tables, and subqueries, you can identify and delete duplicates with precision. Practicing these techniques will ensure your data remains accurate and ready for analysis.

FAQs

• Author
• Recent Posts

Alesha Swift

Alesha Swift is the founder of Codingidol.com, where she simplifies programming concepts with tutorials on Java, Python, SQL, JavaScript, and C. With over 12 years of software development experience, Alesha is passionate about helping learners of all levels master coding skills.

Latest posts by Alesha Swift (see all)

• How to Download SQL Developer on Mac – October 3, 2024
• How to Create Index on SQL Server: A Step-by-Step Guide – October 3, 2024
• How to Create a Non-Clustered Index on Table in SQL Server – October 3, 2024