Find Duplicate Rows in MySQL: Simple Queries for Beginners

Introduction

The article explains how to find duplicate rows in a database, a common issue for beginners, and presents simple SQL solutions.

Defining Duplicate Rows

A duplicate row is defined as a row whose value in a specific column matches the value in another row.

Sample Data

create table test(id int not null primary key, day date not null);
insert into test(id, day) values(1, '2006-10-08');
insert into test(id, day) values(2, '2006-10-08');
insert into test(id, day) values(3, '2006-10-09');
select * from test;

Result:

+----+------------+
| id | day        |
+----+------------+
|  1 | 2006-10-08 |
|  2 | 2006-10-08 |
|  3 | 2006-10-09 |
+----+------------+

Finding Duplicate Rows

Use GROUP BY to group rows with the same column value and HAVING to filter groups whose size is greater than one:

select day, count(*) from test group by day having count(*) > 1;

Result:

+------------+----------+
| day        | count(*) |
+------------+----------+
| 2006-10-08 |        2 |
+------------+----------+

Note: WHERE cannot be used because it filters rows before grouping, while HAVING filters after grouping.

Deleting Duplicate Rows

To keep only the first row (minimum id) and delete the rest, create a temporary table with the minimum id per duplicate group:

create temporary table to_delete(day date not null, min_id int not null);
insert into to_delete(day, min_id)
  select day, MIN(id) from test group by day having count(*) > 1;
select * from to_delete;

Result:

+------------+--------+
| day        | min_id |
+------------+--------+
| 2006-10-08 |      1 |
+------------+--------+

Delete the “dirty” rows:

delete from test where exists (
  select * from to_delete
  where to_delete.day = test.day and to_delete.min_id <> test.id
);

Finding Duplicates on Multiple Columns

When you need rows that have duplicate values in either column b or column c, simple GROUP BY b, c is insufficient. Sample table:

create table a_b_c(
  a int not null primary key auto_increment,
  b int,
  c int
);
-- insert sample data ...

Correct Methods

Union of Separate Queries – query each column independently and combine results:

select b as value, count(*) as cnt, 'b' as what_col from a_b_c group by b having count(*) > 1
union
select c as value, count(*) as cnt, 'c' as what_col from a_b_c group by c having count(*) > 1;

Nested Subqueries with IN – select rows whose b or c appears in a duplicated group:

select a, b, c from a_b_c
where b in (select b from a_b_c group by b having count(*) > 1)
   or c in (select c from a_b_c group by c having count(*) > 1);

Join with Grouped Subqueries – join the original table with subqueries that identify duplicated b and c values:

select a, a_b_c.b, a_b_c.c
from a_b_c
left outer join (
  select b from a_b_c group by b having count(*) > 1
) as b on a_b_c.b = b.b
left outer join (
  select c from a_b_c group by c having count(*) > 1
) as c on a_b_c.c = c.c
where b.b is not null or c.c is not null;

Illustration

Sorting by a column shows how duplicate values are split into different groups, which explains why COUNT(DISTINCT ...) cannot be used directly.