How to Find Duplicate Rows in SQL (and Decide What Counts as One)

Finding duplicate rows in SQL can be a crucial task for data analysis and cleaning. To begin, you must first decide what constitutes a duplicate for the specific table you are working with. Two rows may match on all columns or just on one column, leading to different problems and solutions. The first step is to compare the total row count with the distinct key count using the command COUNT(*) against COUNT(DISTINCT key). If these two numbers differ, it indicates the presence of duplicates in the table.

The general approach to finding duplicates involves grouping rows by the columns that define a duplicate, keeping groups where the COUNT(*) is above one, and marking the extras with ROW_NUMBER instead of deleting them. This process can be visualized in three stages.

Stage one presents a column of eight row boxes, with three rows sharing a small square marker and two rows sharing a different marker, signifying that they have the same key value.

Stage two shows these rows collapsed into five buckets. One bucket contains the three matching rows, another holds the two matching rows, and the remaining three buckets hold a single row each. The two buckets containing more than one row are outlined in a warm warning color and labeled with their counts, three and two.

Finally, Stage three displays only the two flagged buckets, which are then passed through to the final result. This visualization demonstrates that grouping rows by their key makes every duplicate visible as a bucket with a count above one, while unique rows form buckets of one and are removed.

To solidify your understanding, you can use a real example. For instance, a 14-row customers table with three seeded duplicates can be examined using SQLite. Every number in this guide comes from this table, and every query was run against it before its output was pasted here. The goal is for you to confirm every result yourself, as the table is small enough to check by eye. If GROUP BY and HAVING are new concepts to you, it is recommended to read about them first before proceeding.