Read on to learn how to find duplicates in an SQL database and how to delete them.
Create a Sample Database
For demonstration purposes, create a table named Users with a name and score column by running this SQL query.
Insert some sample values by running this query:
Note that some of these rows contain duplicate values for the name column.
Feel free to check out these SQL commands and queries if you need a more in-depth explanation of how to manipulate databases using SQL.
Using GROUP BY to Find Duplicate Values
You can use the GROUP BY statement to arrange values that meet certain conditions in the same group.
Let’s say the names in the sample table have to be unique. You can use GROUP BY to group the rows sharing the same name.
COUNT lets you select the rows that have more than one user with the same name.
When you run this query, the database will return rows containing John and Jane as duplicates.
Deleting Duplicates From a Database
After finding the duplicates, you may want to delete them using the DELETE statement.
For this example, run the following query:
This query uses a CTE expression to find the duplicates and then deletes all of them except one.
Why You Should Delete Duplicate Data
Deleting duplicate data is not a must. However, it lets you free up the space that duplicate rows use.
Fewer rows also mean queries can execute much faster leading to higher performance. Use the queries in this tutorial to help you find and remove duplicates from an SQL database.
