Let’s see how to find duplicate values in an SQL table.
Try Hands-On: Fiddle
Create Source tables: Code Gist
Using GROUP BY
SELECT enrollment_number, COUNT(enrollment_number) as count FROM students GROUP BY enrollment_number HAVING count >1;
GROUP BYenrollment_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.
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 );
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.