What is the SQL Query to Find Duplicate Records in DBMS?

For the SQL query to find duplicate records, the initial task is to specify the duplicate row criterion. Do you want a unique combination of two columns, or are you merely looking for duplication in a single column?

The standard strategy for identifying duplicate values in SQL consists of two main steps:

1. Grouping all rows by the target column(s), i.e., the column(s) you wish to check for duplicate values on, using the GROUP BY clause.
2. Using the COUNT function in the HAVING clause, see which groups have duplicate values (those with more than one item).

The following SQL query to find duplicate records exemplifies the concept:

• Duplication in a single column:

• Finding duplicate values in multiple columns

Transform Your Career

Choose from our industry-leading programs designed for career success

NSDC Certified

Modern Software and AI Engineering Program

Master full-stack development with AI integration

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Modern Data Science and ML with specialisation in AI

Advanced data science techniques with AI specialization

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Advanced AIML with Specialisation in Agentic AI

Deep dive into AIML with focus on Agentic systems

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

DevOps, Cloud & AI Platform Engineering

Build and manage AI-powered cloud infrastructure

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

AI Engineering Advanced Certification by IIT-Roorkee

Premier AI engineering certification from IIT-Roorkee

3 MonthsDuration

AI-LedCurriculum

Career SupportSupport

Go to Program

How to Find Duplicate Values in SQL

1. Create groups of records with identical values to identify duplicate values.
2. Then pick the groups with counts larger than one. That may be done by combining a GROUP BY clause with a HAVING clause.
3. Creating groups of records with identical values across all non-ID columns is the initial stage. To do this, utilize a GROUP BY clause. The names of the columns you wish to utilize for grouping go after the GROUP BY keyword.
4. As long as we have grouped on the appropriate columns, by definition, groups with more than one row must have a duplicate. We employ a HAVING clause to do this. The requirement we set forth is that the group's COUNT(id) element count must be larger than the ones, i.e.,  Id > COUNT(id).
5. Keep in mind that WHERE is used to filter individual rows, whereas HAVING is used to filter groups.

Example of SQL Query to Find Duplicate Records

Our database has a table named Students with data in the following columns: id, name, and gender.

Output

Only duplicate records—those with the same name and gender—are returned by this query:

Output

Amy, a female student, and John, a male student, are both duplicate entries in the table. In the table, the first student occurs three times, and the second student only shows up twice.

Our query produced a list of duplicates in the previous phase. For each duplicate row, we now want to retrieve the whole record.

In order to do this, we must first pick the complete table and then connect it to the duplicate rows. Our query is as follows:

Output

The first SELECT simply picks all of the columns in the user's table and then does an inner join with the duplicate data table from the first query. The two copies of the table must be identified by aliases (in this case, a and b) since we are connecting the table to itself.