How to find duplicate values in SQL Table?

Problem

Let’s see how to find duplicate values in an SQL table.

Input

id	name	enrollment_number
1	Alice	ENR123
2	Bob	ENR124
3	Charlie	ENR125
4	David	ENR123
5	Edward	ENR126
6	Frank	ENR124

Try Hands-On: Fiddle

Create Source tables: Code Gist

Desired Output

enrollment_number	count
ENR123	2
ENR124	2

Solution 1:

Using GROUP BY

SELECT enrollment_number, COUNT(enrollment_number) as count
FROM students
GROUP BY enrollment_number
HAVING count >1;

Explanation:

• GROUP BY enrollment_number: This will create groups of rows based on unique enrollment_number values.

• HAVING count >1: This is a post-grouping filter that allows us to only consider those groups where the count of enrollment_number is more than 1. Hence, it filters the groups to show only the duplicates.

• COUNT(enrollment_number) as count: This will count the number of occurrences of each enrollment_number in the grouped result.

Solution 2:

Using correlated subquery

SELECT name, enrollment_number
FROM students
WHERE enrollment_number IN (
    SELECT enrollment_number 
    FROM students 
    GROUP BY enrollment_number
    HAVING COUNT(enrollment_number) > 1
);

Result:

name	enrollment_number
Alice	ENR123
Bob	ENR124
David	ENR123
Frank	ENR124

Explanation:

The inner query (subquery) selects all the enrollment_numbers that have a count greater than 1, thus identifying duplicates.

The outer query then filters the main table, students, to show only rows where the enrollment_number matches one from the list identified as duplicates in the subquery.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to concatenate multiple rows into one field in MySQL?
2. How to efficiently convert rows to columns in SQL?
3. How to transpose columns to rows in SQL?
4. How to select first row in each GROUP BY group?