{"id":6307,"date":"2017-07-13T23:47:19","date_gmt":"2017-07-14T06:47:19","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6307"},"modified":"2023-12-29T05:32:37","modified_gmt":"2023-12-29T12:32:37","slug":"mysql-find-duplicate-values","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-find-duplicate-values\/","title":{"rendered":"How To Find Duplicate Values in MySQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to find duplicate values of one or more columns in MySQL.<\/p>\n\n\n\n

Data duplication happens because of many reasons. Finding duplicate values is one of the important tasks that you must deal with when working with databases.<\/p>\n\n\n\n

Setting up a sample table<\/h2>\n\n\n\n

First, create a table<\/a> named contacts<\/code> with four columns: id<\/code>, first_name<\/code>, last_name<\/code>, and email<\/code>.<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> contacts (\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n    first_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    last_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n    email VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> contacts (first_name,last_name,email) \nVALUES<\/span> ('Carine '<\/span>,'Schmitt'<\/span>,'carine.schmitt@verizon.net'<\/span>),\n       ('Jean'<\/span>,'King'<\/span>,'jean.king@me.com'<\/span>),\n       ('Peter'<\/span>,'Ferguson'<\/span>,'peter.ferguson@google.com'<\/span>),\n       ('Janine '<\/span>,'Labrune'<\/span>,'janine.labrune@aol.com'<\/span>),\n       ('Jonas '<\/span>,'Bergulfsen'<\/span>,'jonas.bergulfsen@mac.com'<\/span>),\n       ('Janine '<\/span>,'Labrune'<\/span>,'janine.labrune@aol.com'<\/span>),\n       ('Susan'<\/span>,'Nelson'<\/span>,'susan.nelson@comcast.net'<\/span>),\n       ('Zbyszek '<\/span>,'Piestrzeniewicz'<\/span>,'zbyszek.piestrzeniewicz@att.net'<\/span>),\n       ('Roland'<\/span>,'Keitel'<\/span>,'roland.keitel@yahoo.com'<\/span>),\n       ('Julie'<\/span>,'Murphy'<\/span>,'julie.murphy@yahoo.com'<\/span>),\n       ('Kwai'<\/span>,'Lee'<\/span>,'kwai.lee@google.com'<\/span>),\n       ('Jean'<\/span>,'King'<\/span>,'jean.king@me.com'<\/span>),\n       ('Susan'<\/span>,'Nelson'<\/span>,'susan.nelson@comcast.net'<\/span>),\n       ('Roland'<\/span>,'Keitel'<\/span>,'roland.keitel@yahoo.com'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, query data from the contacts<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> contacts\nORDER<\/span> BY<\/span> email;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
In the contacts<\/code> table, we have some rows that have duplicate values in the first_name<\/code>, last_name<\/code>, and email<\/code> columns. Let’s learn how to find them.<\/p>\n\n\n\n
Find duplicate values in one column<\/h2>\n\n\n\n
The find duplicate values in one column of a table, you follow these steps:<\/p>\n\n\n\n
    \n
  1. First, use the GROUP BY<\/a><\/code> clause to group all rows by the target column, which is the column that you want to check duplicate.<\/li>\n\n\n\n
  2. Then, use the COUNT()<\/a><\/code> function in the HAVING<\/a><\/code> clause to check if any group has more than 1 element. These groups are duplicates.<\/li>\n<\/ol>\n\n\n\n
    The following query illustrates the idea:<\/p>\n\n\n
    SELECT<\/span> \n    col<\/span>, \n    COUNT<\/span>(col<\/span>)\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span> col<\/span>\nHAVING<\/span> COUNT<\/span>(col<\/span>) > 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    By using this query template, you can find rows that have duplicate emails in the contacts<\/code> table as follows:<\/p>\n\n\n
    SELECT<\/span> \n    email, \n    COUNT<\/span>(email)\nFROM<\/span>\n    contacts\nGROUP<\/span> BY<\/span> email\nHAVING<\/span> COUNT<\/span>(email) > 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    This picture shows the output of the query that shows the duplicate emails:<\/p>\n\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    Find duplicate values in multiple columns<\/h2>\n\n\n\n
    Sometimes, you want to find duplicate rows based on multiple columns instead of one. In this case, you can use the following query:<\/p>\n\n\n
    SELECT<\/span> \n    col1, COUNT<\/span>(col1),\n    col2, COUNT<\/span>(col2),\n    ...\n\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span> \n    col1, \n    col2, ...\nHAVING<\/span> \n       (COUNT<\/span>(col1) > 1<\/span>) AND<\/span> \n       (COUNT<\/span>(col2) > 1<\/span>) AND<\/span> \n       ...\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    For example, to find rows in the contacts<\/code> table with duplicate values in first_name<\/code>, last_name<\/code>, and email<\/code> column, you use the following query:<\/p>\n\n\n
    SELECT<\/span> \n    first_name, COUNT<\/span>(first_name),\n    last_name,  COUNT<\/span>(last_name),\n    email,      COUNT<\/span>(email)\nFROM<\/span>\n    contacts\nGROUP<\/span> BY<\/span> \n    first_name , \n    last_name , \n    email\nHAVING<\/span>  COUNT<\/span>(first_name) > 1<\/span>\n    AND<\/span> COUNT<\/span>(last_name) > 1<\/span>\n    AND<\/span> COUNT<\/span>(email) > 1<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following illustrates the output of the query:<\/p>\n\n\n\n
    \"MySQL<\/figure>\n\n\n\n
    In this tutorial, you have learned how to find duplicate rows based on the value of one or more columns in MySQL.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
    Rows are considered duplicate only when the combination of columns is duplicate therefore we used the AND<\/code><\/a> operator in the HAVING<\/a><\/code> clause.<\/p>\n\n\n\n
Second, inserts <\/a>rows into the contacts<\/code> table:<\/p>\n\n\n