How to Fetch Duplicate Rows in a Table?

Identifying duplicate rows in a database table is a common requirement, especially when dealing with large datasets. Duplicates can arise due to data entry errors, system migrations, or batch processing issues.

In this article, we will explain efficient SQL techniques to identify and retrieve duplicate rows with detailed explanations and examples.

Finding Duplicate Rows with GROUP BY and HAVING

The GROUP BY and HAVING clauses provide a straightforward way to find duplicate rows. The GROUP BY clause groups rows based on specified columns, and the HAVING clause filters groups that meet a condition, such as appearing more than once. This combination effectively identifies duplicate rows in a table.

Let’s Consider the Below Sample Table tbl

Example 1: Detect Duplicate Rows in a Table

This query groups rows by Name and Section using GROUP BY. The HAVING COUNT(*) > 1 condition filters groups with more than one occurrence, identifying duplicate rows in the tbl table.

Query:

SELECT Name, Section
FROM tbl
GROUP BY Name, Section
HAVING COUNT(*) > 1;

Output

Explanation:

• GROUP BY Name, Section groups the rows based on the combination of Name and Section.
  
• HAVING COUNT(*) > 1 filters groups that occur more than once, identifying duplicates.

Finding Duplicate Values in a Single Column

To find duplicate values in a single column, use the GROUP BY clause to group rows by the column's values and the HAVING clause to filter groups with a count greater than one. This approach efficiently highlights duplicates in the specified column.

Lets Consider the below Sample table PERSON

Example 2: Find Duplicate Names in a Table

This query identifies duplicate names by grouping rows in the PERSON table based on the Name column using GROUP BY. The HAVING COUNT(Name) > 1 condition ensures that only names appearing more than once are included in the output, effectively isolating duplicates.

Query:

SELECT Name
FROM Person
GROUP BY Name
HAVING COUNT(Name) > 1;

Output

Explanation:

• GROUP BY Name groups rows by the Name column.
• HAVING COUNT(Name) > 1 filters groups where the count of names exceeds 1, highlighting duplicates.

Example 3: Detect Duplicates Using a Temporary Table

This query uses a subquery to create a temporary table that counts the occurrences of each Name in the PERSON table. The outer query then filters rows where the count (num) is greater than 1, identifying duplicate names.

Query:

SELECT Name
FROM (
  SELECT Name, COUNT(Name) AS num
  FROM Person
  GROUP BY Name
) AS statistic
WHERE num > 1;

Output

Explanation:

• The subquery SELECT Name, COUNT(Name) AS num FROM Person GROUP BY Name creates a temporary table with the count of each Name.
  
• The outer query filters rows where num > 1, returning duplicate names.

Conclusion

Finding duplicate rows in SQL tables is a crucial skill for maintaining data integrity. Whether using basic GROUP BY and HAVING clauses or Using subqueries for complex scenarios, understanding these techniques ensures efficient and accurate duplicate detection. Apply the method that best fits your use case to optimise performance and streamline database management.