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

tb1

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

PERSON table

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.

FAQs

How to find duplicate rows in a table in SQL?

To find duplicate rows, use GROUP BY with HAVING COUNT(*) > 1. For example:
SELECT column1, column2 FROM table_name
GROUP BY column1, column2 HAVING COUNT(*) > 1;

How do I find duplicates in rows?

Use GROUP BY on the desired column with a HAVING clause to check duplicates. Example:
SELECT column_name FROM table_name
GROUP BY column_name HAVING COUNT(column_name) > 1;

How do I find duplicate rows in an Excel table?

Go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.
Alternatively, use Data > Remove Duplicates to identify and manage duplicates.

S

Sahil Rajput

Improve

Article Tags :

• Databases
• SQL