How do you find duplicate rows in a table?

Q: How do you find duplicate rows in a table?

**Target Audience:** Beginners who know `SELECT` and want the standard duplicate-finding pattern, and candidates who want the details interviewers probe (NULLs, case, collation). **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL behaviour is cited from its manual. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A school is printing ID cards. The office merged three spreadsheets of sign-ups, and now the printer is about to make two cards for Ben and three for Asha. Nobody typed the same row twice on purpose: one form stored `Chen@Example.com`, another `chen@example.com ` with a space on th

The problem

Question presented to candidate: "Our customers table was loaded from three different sign-up forms and we think some people are in it more than once. How would you find the duplicates with SQL, and what exactly counts as a duplicate?"

What a strong answer should cover:

Define the duplicate first: which columns make two rows "the same" (here the email), since the surrogate id is always unique
The core pattern: GROUP BY the key columns, then HAVING COUNT(*) > 1 to keep only groups with more than one row
Seeing the full duplicate rows, not just the keys: a window COUNT(*) OVER (PARTITION BY ...), or a join/EXISTS back to the grouped keys
Dirty data: case and stray spaces (LOWER(TRIM(email))), and that GROUP BY puts all NULLs in one group even though NULL = NULL is not true
Engine detail: PostgreSQL and SQLite compare text case-sensitively by default, MySQL default collation does not
Prevention: a UNIQUE constraint or unique expression index once the data is clean

Clarifying questions expected:

Which columns define a duplicate: the whole row, or a business key like the email?
Should Asha@Example.com and asha@example.com count as the same person?
Do you only want to report duplicates, or also remove them afterwards?

Code / implementation expected: Yes — a GROUP BY ... HAVING COUNT(*) > 1 query, plus one way to list the full duplicate rows.

Schema used by the examples:

duplicates
group-by

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know SELECT and want the standard duplicate-finding pattern, and candidates who want the details interviewers probe (NULLs, case, collation). Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL behaviour is cited from its manual.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A school is printing ID cards. The office merged three spreadsheets of sign-ups, and now the printer is about to make two cards for Ben and three for Asha. Nobody typed the same row

The solution is hidden so you can try first

9 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLThe core pattern: group by the key, keep groups with more than one row

SQLEvery duplicate row in full, matching emails case- and space-insensitively

SQLDuplicates across several columns, with the ids in each group (SQLite GROUP_CONCAT)