{"id":46289,"date":"2018-12-28T11:20:13","date_gmt":"2018-12-28T05:50:13","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=46289"},"modified":"2021-03-11T18:05:42","modified_gmt":"2021-03-11T12:35:42","slug":"duplicate-records-in-sql","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/","title":{"rendered":"How to Find Duplicate Records in SQL – With & Without DISTINCT Keyword"},"content":{"rendered":"

In this tutorial, we will learn about duplicates and the reasons we need to eliminate them. Also, we will focus on the methods with which we can remove duplicates from the data we have in our SQL database.<\/p>\n

What are Duplicates in SQL?<\/h3>\n

Duplicates in SQL are mostly the data points that exist more than once in our data store.<\/p>\n

For example: <\/strong><\/p>\n

If we consider a customer table and in it, we store the details of customers of any particular shop. Then here, duplicate records would be the entry of a single customer more than once.<\/p>\n

In such cases, the system has an increased load of handling large data stores and their processing.<\/p>\n

Why do we need to handle Duplicates in SQL?<\/h3>\n

Some of the major reasons why we need to remove duplicates from our records are as follows:<\/p>\n

1. The size of data to be stored increases due to the duplicates.<\/p>\n

2. When we have duplicates in our data they can give rise to business errors also known as logical errors.<\/p>\n

3. Due to the increased use of resources, the overall cost of the handling resources rises.<\/p>\n

4. Errors arise when analytics are performed on such erroneous data.<\/p>\n

Demo Database<\/strong><\/p>\n

Let us now have a look at our demo database: DataFlair.
\nQuery:<\/strong><\/p>\n

USE DataFlair;\r\nSELECT * FROM dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"Handle<\/a><\/p>\n
How to handle Duplicates in SQL?<\/h3>\n
We have various solutions present to handle the duplicates which exist in our database. Some of them are as follows:<\/p>\n
1. Using the Distinct Keyword to eliminate duplicate values and count their occurences from the Query results.<\/h4>\n
We can use the Distinct keyword to fetch the unique records from our database. This way we can view the unique results from our database.
\nSyntax:<\/strong><\/p>\n
SELECT col1, col2, COUNT(DISTINCT(col3)),.....\r\nFROM tableName;\r\n<\/pre>\n
Example:<\/strong> Let us first try to view the count of unique id\u2019s in our records by finding the total records and then the number of unique records.
\nStep 1:<\/strong> View the count of all records in our database.
\nQuery:<\/strong><\/p>\n
USE DataFlair;\r\nSELECT COUNT(emp_id) AS total_records FROM dataflair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"SQL<\/a><\/p>\n
Step 2:<\/strong> View the count of unique records in our database.
\nQuery:<\/strong><\/p>\n
USE DataFlair;\r\nSELECT COUNT(DISTINCT(emp_id)) AS Unique_records FROM DataFlair;\r\nSELECT DISTINCT(emp_id) FROM DataFlair;\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"Handling<\/a><\/p>\n
2. Using Distinct keyword to delete the Duplicate records from the database.<\/h4>\n
Syntax:<\/strong><\/p>\n
SELECT col1, col2, DISTINCT(col3),.....\r\nFROM tableName;\r\n<\/pre>\n
Example: <\/strong>Let us now view our table after deleting the duplicate records in our database.
\nQuery:<\/strong><\/p>\n
USE DataFlair;\r\nSELECT DISTINCT(emp_id),name,location,experience FROM DataFlair\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"Handling<\/a><\/p>\n
3. Using the Count keyword and Group By to eliminate the duplicate records.<\/h4>\n
Syntax:<\/strong><\/p>\n
SELECT col1,col2,col3,......\r\nCOUNT(*) AS aliasName\r\nFROM tableName\r\nGROUP BY col1,col2,col3,......\r\nHAVING COUNT(*)  = 1;\r\n<\/pre>\n
Example:<\/strong> Let us now eliminate the duplicate records of our database using the Count and the Group By keyword.
\nQuery:<\/strong><\/p>\n
USE DataFlair;\r\nSELECT emp_id,name,location,experience, COUNT(*) as Count\r\nFROM DataFlair\r\nGROUP BY emp_id,name,location,experience\r\nHAVING COUNT(*)  = 1\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"Duplicates<\/a><\/p>\n
4. Using Joins to eliminate the Duplicate records.<\/h4>\n
Syntax:<\/strong><\/p>\n
SELECT col1,col2,col3,....\r\nFROM tableName alias\r\nWHERE EXISTS(SELECT 1 FROM tableName alias2 \r\nWHERE Alias.col1 = Alias1.col1 AND\r\nAlias.col2 = Alias2.col2 AND\r\nAlias.col2 = Alias2.col2 AND\r\n...);\r\n<\/pre>\n
Example:<\/strong> Let us now try to remove the duplicates from our data using Join operation on our data.
\nQuery:<\/strong><\/p>\n
USE DataFlair;\r\nSELECT emp_id,name,location,experience\r\nFROM DataFlair A1\r\nWHERE EXISTS (SELECT 1\r\n        FROM DataFlair A2\r\n        where A1.emp_id = A2.emp_id\r\n        AND A1.name = A2.name \r\n        AND A1.location = A2.location\r\n        AND A1.experience = A2.experience);\r\n<\/pre>\n
Output:<\/strong><\/p>\n
\"Handle<\/a><\/p>\n
Summary<\/h3>\n
In this tutorial, we have understood what are duplicates and how do they affect our business. We have then also discussed various methods by which we can either handle or completely remove duplicates from our database.<\/p>\n
We have various methods like the Distinct keyword and then we have also seen the use of count and the Group By clause. And at last, we have seen how we can use joins to eliminate duplicates from our data.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this tutorial, we will learn about duplicates and the reasons we need to eliminate them. Also, we will focus on the methods with which we can remove duplicates from the data we have...<\/p>\n","protected":false},"author":6,"featured_media":86957,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[13447,18112,18108,18110,18111,18113,18109],"class_list":["post-46289","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-sql-distinct-keyword","tag-sql-duplicate","tag-sql-duplicate-records","tag-sql-duplicate-row","tag-sql-find-duplicate-rows","tag-sql-find-duplicates","tag-sql-query-to-find-duplicate-rows-in-a-database"],"yoast_head":"\nHow to Find Duplicate Records in SQL - With & Without DISTINCT Keyword - DataFlair<\/title>\n<meta name=\"description\" content=\"How to find Duplicate Records in SQL - with and without DISTINCT Keyword,delete duplicate rows using group by,delete duplicate rows using self join\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Find Duplicate Records in SQL - With & Without DISTINCT Keyword - DataFlair\" \/>\n<meta property=\"og:description\" content=\"How to find Duplicate Records in SQL - with and without DISTINCT Keyword,delete duplicate rows using group by,delete duplicate rows using self join\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-12-28T05:50:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-11T12:35:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/12\/SQL-Handling-Duplicates-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Find Duplicate Records in SQL - With & Without DISTINCT Keyword - DataFlair","description":"How to find Duplicate Records in SQL - with and without DISTINCT Keyword,delete duplicate rows using group by,delete duplicate rows using self join","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/","og_locale":"en_US","og_type":"article","og_title":"How to Find Duplicate Records in SQL - With & Without DISTINCT Keyword - DataFlair","og_description":"How to find Duplicate Records in SQL - with and without DISTINCT Keyword,delete duplicate rows using group by,delete duplicate rows using self join","og_url":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-12-28T05:50:13+00:00","article_modified_time":"2021-03-11T12:35:42+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/12\/SQL-Handling-Duplicates-1.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"How to Find Duplicate Records in SQL – With & Without DISTINCT Keyword","datePublished":"2018-12-28T05:50:13+00:00","dateModified":"2021-03-11T12:35:42+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/"},"wordCount":511,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/12\/SQL-Handling-Duplicates-1.jpg","keywords":["SQL Distinct Keyword","sql duplicate","SQL Duplicate Records","sql duplicate row","sql find duplicate rows","sql find duplicates","sql query to find duplicate rows in a database"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/","url":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/","name":"How to Find Duplicate Records in SQL - With & Without DISTINCT Keyword - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/12\/SQL-Handling-Duplicates-1.jpg","datePublished":"2018-12-28T05:50:13+00:00","dateModified":"2021-03-11T12:35:42+00:00","description":"How to find Duplicate Records in SQL - with and without DISTINCT Keyword,delete duplicate rows using group by,delete duplicate rows using self join","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/12\/SQL-Handling-Duplicates-1.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/12\/SQL-Handling-Duplicates-1.jpg","width":1200,"height":628,"caption":"SQL Handling Duplicates (1)"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/duplicate-records-in-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SQL Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sql\/"},{"@type":"ListItem","position":3,"name":"How to Find Duplicate Records in SQL – With & Without DISTINCT Keyword"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/46289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=46289"}],"version-history":[{"count":9,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/46289\/revisions"}],"predecessor-version":[{"id":86968,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/46289\/revisions\/86968"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/86957"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=46289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=46289"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=46289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}