How to discovery duplicate records inwards tabular array is a pop SQL interview enquiry which has been asked every bit many times every bit difference betwixt truncate together with delete inwards SQL or finding instant highest salary of employee. Both of these SQL queries are must know for whatever i who is appearing on whatever programming interview where to a greater extent than or less questions on database together with SQL are expected. In guild to find duplicate records inwards database table you lot demand to confirm Definition of duplicates, for instance inwards below contact tabular array which is suppose to shop name together with phone number of contact, a tape is considered to live on duplicate if both refer together with band pose out is same but unique if either of them varies. Problem of duplicates inwards database arise when you lot don't receive got a primary fundamental or unique key on database together with that's why its recommended to receive got a fundamental column inwards table. Anyway its tardily to discovery duplicate records inwards tabular array yesteryear using group yesteryear clause of ANSI SQL. Group yesteryear clause is used to grouping information based upon whatever column or a pose out of columns. Here inwards guild to locate duplicate records nosotros demand to role group yesteryear clause on both name together with phone every bit shown inwards second SQL SELECT query example. You tin run across inwards outset query that it listed Ruby every bit duplicate tape fifty-fifty though both Ruby receive got dissimilar band pose out because nosotros solely performed grouping yesteryear on name. Once you lot receive got grouped information you lot tin filter out duplicates yesteryear using having clause. Having clause is counter business office of where clause for aggregation queries. Just recall to furnish temporary refer to count() information inwards guild to role them inwards having clause.
SQL Query to discovery duplicate records inwards a tabular array inwards MySQL
In this department nosotros volition run across SQL query which tin live on used to locate duplicate records inwards table. As explained inwards previous section, Definition of duplicate depends upon trouble concern rules which must live on used inwards grouping yesteryear clause. In next query nosotros receive got used SELECT query to choose all records from Contacts table. Here James, Johnny, Harry together with Ron are duplicated 4 times. mysql> choose * 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 inwards ready (0.00 sec)
Following SELECT query volition only discovery duplicates records based on name which powerfulness non live on right if ii contact of same but dissimilar numbers are stored, every bit inwards next upshot ready Ruby is shown every bit duplicate which is incorrect.
mysql> choose name, count(name) from contacts grouping yesteryear name;
+-------+-------------+
| name | count(name) |
+-------+-------------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
| Ruby | 2 |
+-------+-------------+
5 rows inwards ready (0.00 sec)
This is the right means of finding duplicate contacts at it aspect both refer together with band number together with solely impress duplicate if both refer together with band is same.
mysql> choose name, count(name) from contacts grouping yesteryear name, phone;
+-------+-------------+
| name | count(name) |
+-------+-------------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
| Ruby | 1 |
| Ruby | 1 |
+-------+-------------+
6 rows inwards ready (0.00 sec)
having clause inwards SQL query volition filter duplicate records from non duplicate records. As inwards next query it impress all duplicate records together with how many times they are duplicated inwards table.
mysql> choose name, count(name) every bit times from contacts grouping yesteryear name, band having times>1;
+-------+-------+
| name | times |
+-------+-------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
+-------+-------+
4 rows inwards ready (0.00 sec)
That's all on how to discovery duplicate records inwards table, These SQL queries volition operate on all database similar MySQL, Oracle, SQL Server together with Sybase every bit it solely uses ANSI SQL together with doesn't role whatever database specific feature. Another interesting SQL query interview enquiry is "How to delete duplicate records from table" which nosotros volition run across inwards to a greater extent than or less other post.