Table of contents
1.
Introduction
2.
What are Duplicate Records?
3.
How to Find Duplicate Records in SQL?
3.1.
MySQL
4.
How to Delete Duplicate Records in SQL?
4.1.
1. Using the DISTINCT Keyword
4.2.
MySQL
4.3.
MySQL
4.4.
2. Using the Group By and Having Clause
4.5.
3. Using the INNER JOIN Statement
4.6.
MySQL
5.
Frequently Asked Questions
5.1.
How to delete duplicate records in SQL?
5.2.
How to delete duplicate records in SQL using subquery?
5.3.
How to delete duplicate records in MySQL?
5.4.
What is the use of GROUP BY in SQL?
6.
Conclusion
Last Updated: Mar 27, 2024
Easy

Delete Duplicate Records in SQL

Author Muskan Sharma
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Hey Readers!!

Just imagine you are working on a database, and while entering the data in your database, you've added the same data in multiple rows. Now what will you do? 

The duplicity in the database will lead to the complexity of the database and increases the size of the database. To resolve this issue, we need to delete duplicate records in SQL.

delete duplicate records in sql

In this article, you will get to learn different ways in which you can delete duplicate records in SQL. 

So let us dive into the topic to explore more about it.

What are Duplicate Records?

Duplicate records or rows are the rows that are identical in the database. In simple terms, we can say that these are one or more records that represent the same object in the database.

For example, in a company's database, there are two Ram. One is Ram Kapoor, and the other one is Ram Malhotra, and they have the same DOB and joining date. While entering their details into the database. The manager has added the same surname after their names, which resulted in duplicate records. To resolve this, we have to delete the duplicate records. 

Let us look at how we can delete duplicate records in SQL.

How to Find Duplicate Records in SQL?

We can find duplicate records in Sql using the GROUP BY and HAVING clauses. First, let us create a table with the following commands:

  • MySQL

MySQL

CREATE TABLE CodingNinjas (
Name varchar(90),
ID int,
Address varchar(255),
Email varchar(90),
PhoneNo int,
DOJ DATE
);
insert into CodingNinjas values('Jatin',12345,'Bengaluru','jatin@gmail.com',9876543211,'2023-05-01');
insert into CodingNinjas values('Priyam',12344,'Chennai','priyam@gmail.com',9876523211,'2023-04-20');
insert into CodingNinjas values('Shubham',12345,'Delhi','shubham@gmail.com',9870983211,'2023-03-19');
insert into CodingNinjas values('Ram',12346,'Ranchi','ram@gmail.com',9871233211,'2023-05-05');
insert into CodingNinjas values('Shayam',12347,'Pune','shyam@gmail.com',9876543209,'2023-04-10');
insert into CodingNinjas values('Rohit',12348,'Chandigarh','rohit@gmail.com',9876543456,'2023-02-02');
insert into CodingNinjas values('Jatin',12345,'Bengaluru','jatin@gmail.com',9876543211,'2023-05-01');
select * from CodingNinjas;
You can also try this code with Online MySQL Compiler
Run Code

 

OUTPUT

Table created

We can see that Jatin has duplicate records. Now, we can find the duplicate record in this table with the following command:

select * from CodingNinjas GROUP BY ID HAVING COUNT(Name)>1;

 

OUTPUT

duplicate record found out

As we can see that the duplicate record has been found out.

Also see, SQL EXCEPT

How to Delete Duplicate Records in SQL?

Here are some ways in which we can delete duplicate records in SQL.

  • Using the DISTINCT Keyword
     
  • Using the Group By and Having Clause
     
  • Using the INNER JOIN statement
     

Let us look at each one of them one by one.

1. Using the DISTINCT Keyword

As the name suggests, the DISTINCT keyword returns a number of distinct from one or more columns. To delete duplicate records in SQL, we can use DISTINCT in the following manner.

Step 1: Create the Table named CodingNinjas having details of the employees like their name, ID, address, email, phone number, and date of joining.

  • MySQL

MySQL

CREATE TABLE CodingNinjas (
Name varchar(90),
ID int,
Address varchar(255),
Email varchar(90),
PhoneNo int,
DOJ DATE
);
You can also try this code with Online MySQL Compiler
Run Code
Output of table

Step 2: 

Now insert values in the table.

  • MySQL

MySQL

insert into CodingNinjas values('Jatin',12345,'Bengaluru','jatin@gmail.com',9876543211,'2023-05-01');
insert into CodingNinjas values('Priyam',12344,'Chennai','priyam@gmail.com',9876523211,'2023-04-20');
insert into CodingNinjas values('Shubham',12345,'Delhi','shubham@gmail.com',9870983211,'2023-03-19');
insert into CodingNinjas values('Ram',12346,'Ranchi','ram@gmail.com',9871233211,'2023-05-05');
insert into CodingNinjas values('Shayam',12347,'Pune','shyam@gmail.com',9876543209,'2023-04-10');
insert into CodingNinjas values('Rohit',12348,'Chandigarh','rohit@gmail.com',9876543456,'2023-02-02');
insert into CodingNinjas values('Jatin',12345,'Bengaluru','jatin@gmail.com',9876543211,'2023-05-01');
You can also try this code with Online MySQL Compiler
Run Code


Step 3: Print the CodingNinjas table.

SELECT * FROM CodingNinjas;

 

Output of table with values


Step 4: Getting the unique values from the table by using the DISTINCT keyword.
 

SELECT DISTINCT * FROM CodingNinjas;

 

Output of unique tables

Explanation: In the above table, we have two records of Jatin. After using the DISTINCT keyword, it prints only one record of Jatin.

2. Using the Group By and Having Clause


Using the Group By and Having Clause, we can print the record occurring more than once in the database by using the count keyword.

SELECT COUNT(ID), Name, Email, PhoneNo, Address
FROM CodingNinjas
GROUP BY Name, Email, PhoneNo, Address
HAVING COUNT(ID) > 1;

 

Output of By Using Group By and Having Clause

Explanation: The Group By and Having Clause prints the occurrence of Jatin's record because the count of Jatin's is more than 1, as the count condition checks ID>1. 

3. Using the INNER JOIN Statement

Let us create a table with the following commands:

  • MySQL

MySQL

CREATE TABLE CodingNinjas (
Name varchar(90),
ID int,
Email varchar(90)
);
insert into CodingNinjas values('Jatin', 1, 'jatin@gmail.com');
insert into CodingNinjas values('Jatin', 2, 'jatin@gmail.com');
You can also try this code with Online MySQL Compiler
Run Code

 

The table is as follows:

table

Now, let us delete the duplicate entry for Jatin using the following commands:

delete a
from CodingNinjas a
INNER JOIN
CodingNinjas b
ON a.Name=b.Name
AND a.ID>b.ID;

 

In the above command, we deleted the record using an INNER JOIN on the table itself(SELF JOIN) where the Name is the same, but the ID is different. The one with the bigger value of ID is deleted.

OUTPUT

inner join delete

Now, let us see our table with the following command:

select * from CodingNinjas;

 

OUTPUT

final result after delete using inner join

We can see that there are no duplicate entries in the table now.

Frequently Asked Questions

How to delete duplicate records in SQL?

In SQL, we can delete duplicate records using various methods. We can use the DISTINCT keyword, the GROUP BY and the HAVING clause, or we can use the INNER JOIN statement for it.

How to delete duplicate records in SQL using subquery?

The outer query will delete the records from the table, which will not be a part of the result of the inner query. The inner query will give all the duplicate records of the table except for any one of them.

How to delete duplicate records in MySQL?

In MySQL, we can delete duplicate records using the SSELF JOINS, Common Table Expressions(CTE), Subqueries, temporary tables, and analytic functions. You can also view unique records by DISTINCT keyword, GROUP BY, and HAVING clauses.

What is the use of GROUP BY in SQL?

In SQL, the GROUP BY clause groups data based on one or more columns. It is commonly used with aggregate functions such as SUM, COUNT, AVG, MIN, and MAX to group rows with similar values together.

Conclusion

In this article, you have learned what duplicate records are in SQL and different ways to delete duplicate records in SQL. We hope this article helped you enhance your knowledge about different ways of deleting duplicate records in SQL. If you want to learn more, refer to these articles:

 

Happy Learning, Ninja!

Live masterclass