Lesson 9 of 13 · 20 min
Finding duplicates
By the end of this lesson you can choose a duplicate key, normalise it, find duplicate groups with GROUP BY and HAVING, list all the rows in those groups, and judge whether a match is a real duplicate or a coincidence.
A table's primary key guarantees that no two rows are identical, but it says nothing about whether two rows describe the same real thing. Wadi Market's customers table has a unique customer_id per registration, yet a few people registered twice: same name, same phone, and an email typed once as nada.kapoor591@example.com and once with a trailing space.
Finding duplicates always follows the same pattern:
- Choose a key: the column or columns that identify the real-world thing (email, phone, national ID, name + birth year).
- Normalise it, so trivial differences (case, spaces) do not hide a match.
- Group by the key and keep groups with more than one row:
HAVING count(*) > 1.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons