Friday, December 14, 2012

How to find duplicate records in a table on database - SQL tips

How to find duplicate records in table is a popular SQL interview question which has been asked as many times as difference between truncate and delete in SQL or finding second highest salary of employee. Both of these SQL queries are must know for any one who is appearing on any programming interview where some questions on database and SQL are expected. In order to find duplicate records in database table you need to confirm definition of duplicates, for example in below contact table which is suppose to store name and phone number of contact, a record is considered to be duplicate if both name and phone number is same but unique if either of them varies. Problem of duplicates in database arise when you don't have a primary key or unique key on database and that's why its recommended to have a key column in table. Anyway its easy to find duplicate records in table by using group by clause of ANSI SQL. Group by clause is used to group data based upon any column or a number of columns. Here in order to locate duplicate records we need to  use group by clause on both name and phone as shown in second SQL SELECT query example. You can see in first query that it listed Ruby as duplicate record even though both Ruby have different phone number because we only performed group by on name. Once you have grouped data you can filter out duplicates by using having clause. Having clause is counter part of where clause for aggregation queries. Just remember to provide temporary name to count() data in order to use them in having clause.

SQL Query to find duplicate records in a table in MySQL

How to find duplicate records in mysql database with exampleIn this section we will see SQL query which can be used to locate duplicate records in table. As explained in previous section, definition of duplicate depends upon business rules which must be used in group by clause. In following query we have used SELECT query to select all records from Contacts table. Here James, Johnny, Harry and Ron are duplicated four times.

mysql> select * from Contacts;
+-------+----------+
| name  | phone    |
+-------+----------+
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron   | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron   | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron   | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron   | 44446666 |
| Ruby  |  8965342 |
| Ruby  |  6888342 |
+-------+----------+
18 rows in set (0.00 sec)

Following SELECT query will only find duplicates records based on name which might not be correct if two contact of same but different numbers are stored, as in following result set Ruby is shown as duplicate which is incorrect.

mysql> select name, count(name) from contacts group by name;
+-------+-------------+
| name  | count(name) |
+-------+-------------+
| Harry |           4 |
| James |           4 |
| Johnny |           4 |
| Ron   |           4 |
| Ruby  |           2 |
+-------+-------------+
5 rows in set (0.00 sec)

This is the correct way of finding duplicate contacts at it look both name and phone number and only print duplicate if both name and phone is same.

mysql> select name, count(name) from contacts group by name, phone;
+-------+-------------+
| name  | count(name) |
+-------+-------------+
| Harry |           4 |
| James |           4 |
| Johnny |           4 |
| Ron   |           4 |
| Ruby  |           1 |
| Ruby  |           1 |
+-------+-------------+
6 rows in set (0.00 sec)

having clause in SQL query will filter duplicate records from non duplicate records. As in following query it print all duplicate records and how many times they are duplicated in table.

mysql> select name, count(name) as times from contacts group by name, phone having times>1;
+-------+-------+
| name  | times |
+-------+-------+
| Harry |     4 |
| James |     4 |
| Johnny |     4 |
| Ron   |     4 |
+-------+-------+
4 rows in set (0.00 sec)

That's all on how to find duplicate records in table, These SQL queries will work on all database like MySQL, Oracle, SQL Server and Sybase as it only uses ANSI SQL and doesn't use any database specific feature. Another interesting SQL query interview question is "How to delete duplicate records from table" which we will see in another post.

Other SQL and database articles you may find useful

1 comment :

Vivek Hingorani said...

Hi Javin.. Can you please write something about DB performance tuning as well as its being repetitvely asked to me and am unable to convince a great deal as I havent really worked on such scenarios

Post a Comment