2 Ways to FIND and DELETE Duplicate Rows in a SQL Table | LeetCode SQL

Key Takeaways

try this new and more efficient way to delete duplicate rows I will use the lead code SQL interview question 196 delete duplicate emails as an example here I'm going to present two approaches to solve this problem the last one is the most efficient and is the best suited for tables with large data so please watch this video until the end according to the question we need to create a person table with two columns ID and email the ID column is the primary key which means that each row must have a unique ID value now let's populate this table with some data there are 10 rows and some of them are duplicates let's read the question further the task is to write a solution to delete all duplicate emails keeping only one unique email with the smallest ID for example the email address Raj example.com is associated with id8 while its duplicate entry has ID 1 therefore we should remove the entry with ID 8 and retain the one with ID 1 as it represents the smallest ID now let's quickly write a select a statement to find all the unique emails with the smallest ID here we are choosing the smallest minimum ID for each group of the people who shares the same email this tells SQL that we are working with the table named person we are organizing a list of people people into groups based on their email addresses each group includes people who have the same email this way we ensures that each email appears only once in our final list and we pick the smallest ID for each group it's handy for finding and managing duplicate email entries in our data let's execute out of the total of 10 records seven are unique emails it's evident in the result set that rose with ID 58 eight and 10 are absent indicating that they represents duplicate entries we will save this statement because we will need it later now there are two approaches to handle the problem of removing duplicate records the first one is The Beginner's approach in this approach we will Begin by creating a new table this table will store these unique emails along with their corresponding IDs to create this table I will use the create table as a statement along with the select statement we just discussed after execution we will have a table named tempore person containing all the unique emails with the smallest ID let's verify now we will use the data of this table to delete all the duplicate records let's write the delete statement this is a simple delete statement in the where Clause we will use the not in operator with a subquery after execution any Row in the person table with IDs that do not exist in the tempore person table will be removed it's important to note that in the tempore person table the IDS of duplicate rows are not present therefore this delete operation will effectively remove all the duplicate rows let's execute three rows deleted let's confirm this by examining the person table as you can see in the result set only the rules with the smallest ID have remained and all the duplicate rules have been successfully deleted however while this method gets the job done it doesn't allly with professional SQL development practices and is not the most efficient approach let me tell you why first it makes a new temporary table which can take up a lot of space if your original table has millions of of roles this means it's not very efficient and can also slow things down because it uses a lot of storage if your original table has indexes they will also make the temporary table even bigger second it involves writing two separate queries which can be more complicated and take more work this makes it harder to understand and maintain we are creating a SQL statement to remove duplicate record here's how it goes this part tells SQL that we want to delete some data from the person table this is where we specify the condition for deleting duplicates we will compare the combination of ID and email columns in the table then we have not in operator then we put parenthesis to enclose the special select statement this select a statement finds the unique rowes based on the email column this this is the same select statement that we wrote in the starting of the tutorial let's copy it from here and paste it inside the parenthesis the not in condition checks if the combination of ID and email is not in the list of unique combination found by our select statement so we are simply telling SQL to delete rows in the person table where the combination of ID and email is not among the unique combination found by our selected statement let's execute three rows deleted this SQL statement is a handy way to keep our data Clean and Free from duplicates this is the efficient approach because it doesn't involve saving data into a separate table which means no additional storage or iio overhead it's also simple and easy to understand let's check this statement by submitting it on lead code submitted successfully if you want to learn how to quickly find the number of duplicate records in a table using just one line of code then watch my this short video I explained it in just 50 seconds Link in the description that's it for today thanks for watching this is Manish from Rebellion rider.com