Find Duplicates in SQL





SQL does not provide a built-in capability to find duplicates in a table. Fortunately, it is fairly easy to write a SQL query that does it. The idea is to count the number of occurrences for each value, and then use the HAVING condition to show only the values that appear more than once.

SELECT COLUMN_NAME, COUNT(*)
FROM TABLE_NAME
GROUP BY COLUMN_NAME
HAVING (COUNT(*) > 1);

To extend this a step further, if you want to retrieve the list of all records that have duplicate values, you can use the SQL statement below, which contains a subquery and includes an inline view:

SELECT *
FROM TABLE_NAME
WHERE COLUMN_NAME IN
(SELECT COLUMN_NAME FROM
(SELECT COLUMN_NAME, COUNT(*)
FROM TABLE_NAME
GROUP BY COLUMN_NAME
HAVING (COUNT(*) > 1) VIEW_NAME
);

Essentially the original query to get the list of values with more than one occurrence was set up as an inline view for the query that would get just the COLUMN_NAME (not the count). It’s necessary to do this because the IN clause we use means that the subquery should only return a single column.

Tags: Tags: , , , ,