Find Duplicate Records

Interviewers love this one because duplicate rows are a silent data bug: they can double-charge, double-email, or make reports lie.

Question: Find Duplicate Records in SQL.

Answer: The usual approach is to define what makes a row duplicate, then group by those columns and keep only groups with more than one row. If you need the actual duplicate rows, not just the duplicated values, use ROW_NUMBER() to label rows inside each group and keep the rows after the first one. Watch out for NULL, case, and spaces, because they can change whether two rows should be treated as the same.

Interview-Ready Answer: I’d first clarify whether you mean duplicate business keys or exact duplicate rows. Then I’d usually use GROUP BY ... HAVING COUNT(*) > 1 to find the repeated values, or a ROW_NUMBER() window function to return the duplicate rows themselves. In a real system I also normalize data when needed, like trimming spaces or lowercasing emails, and I pay special attention to NULL handling because that is a common gotcha.

What counts as a duplicate?

Detailed Explanation: The first step is not writing SQL; it is deciding the rule. A row may be a duplicate because the same business key appears twice, such as the same email, the same order number, or the same product code. A row may also be an exact duplicate, meaning every meaningful column matches except a surrogate key like id. If you skip this definition, you may find the wrong rows and “fix” data that was actually valid.

How it works under the hood

1. Choose the key columns. Ask, “What should be unique?” For customers it might be email; for order lines it might be order_id, line_number.
2. Normalize noisy data. Use expressions like LOWER(TRIM(email)) if the business treats Ana@x.com and ana@x.com as the same person.
3. Bucket rows together. GROUP BY creates buckets of equal values, while ROW_NUMBER() OVER (PARTITION BY ...) puts a sequence number inside each bucket.
4. Count each bucket. COUNT(*) tells you how many rows are in the group. If the count is greater than 1, you have a duplicate group.
5. Filter the repeats. Use HAVING COUNT(*) > 1 to return duplicate values, or WHERE rn > 1 to return the extra rows after the first.
6. Decide which row survives. When using ROW_NUMBER(), you must choose an ORDER BY rule, often the oldest row, newest row, or smallest id. That rule becomes your “winner.”

Common patterns compared

Performance and complexity

On a big table, the database usually has to inspect the relevant rows at least once, so think of this as a full scan unless an index helps. In practice, the engine may use a hash aggregate (group rows in memory by key) or a sort (order rows first, then count consecutive matches). That means the cost is often around O(n) to O(n log n) time depending on the plan, with extra memory or disk use if the data is large; for example, 10 million rows can fit in memory on one system and spill to disk on another. An index on the duplicate key can help a lot, especially when you are checking a narrow business key like email, but no index can save you if you must compare many wide columns.

Important edge cases

• NULL values: GROUP BY puts nulls in one bucket, but equality joins do not match nulls the same way. If null should count as one duplicate group, make that explicit with COALESCE.
• Case and spaces: bob@example.com and bob@example.com look the same to humans but not to SQL unless you normalize them.
• Choosing the wrong columns: If you include a surrogate key like id in the duplicate check, every row looks unique and you miss the problem.
• COUNT(column) vs COUNT(*): COUNT(column) skips nulls, so it can hide bad data. COUNT(*) counts rows.

Memory aid: Think of duplicate detection like sorting tickets at a coat check: first you group the same ticket numbers together, then you count how many copies you have, and if there is more than one, the extras are duplicates.

Real-World Example: Imagine a checkout service for an e-commerce site. Customer emails are supposed to be unique, because the billing system, loyalty program, and receipt sender all use that key. One night, support notices that some shoppers received two welcome emails and two loyalty points entries. The root cause was a data import job that compared raw email strings, so ana@example.com, ANA@example.com, and ana@example.com were treated as three different customers. In logs, the team saw repeated insert attempts and a spike in duplicate account creation; in the app, users complained about duplicate receipts and support tickets rose within minutes. The fix was to detect duplicates using a normalized email key and to add a unique constraint after cleanup, so the bug could not return silently.

Follow-up & Tricky Questions:

• How do you find duplicate rows across multiple columns? Group by all columns that define uniqueness, such as order_id, line_number, or partition by the same list in ROW_NUMBER(). The rule is simple: whatever makes one record unique must appear in the duplicate check.
• How do you return only one row per duplicate group? Use ROW_NUMBER() and keep rn = 1 for the row you want to preserve. Usually that row is chosen by earliest timestamp or smallest id.
• How do you delete duplicates but keep one copy? Wrap the ROW_NUMBER() logic in a CTE or subquery, then delete rows where rn > 1. This is safer than deleting with a plain join because the ranking makes the winner explicit.
• How do you make duplicate detection case-insensitive? Normalize the key with LOWER() or use a case-insensitive collation if your database and business rules support it. The important part is consistency: detect duplicates the same way the application writes the data.
• How do indexes help? An index on the duplicate key can reduce sorting work and speed up grouping, especially for a narrow key like email. But the database still needs to inspect the rows or index entries, so duplicates are never truly free to find on a large table.
• Does COUNT(email) work? Not reliably if email can be null, because COUNT(column) ignores nulls. Use COUNT(*) when you want to count rows, not just non-null values.
• Does DISTINCT find duplicates? No, DISTINCT removes repeated results but does not tell you which rows were duplicated. It is a display tool, not a diagnosis tool.
• Are two rows with the same email but different ids duplicates? Only if email is the business key. A surrogate id is usually just a row identifier, not the thing that should be unique from a business point of view.

Common Mistakes:

• Using the primary key in the duplicate check. Fix: exclude surrogate keys like id and group by the business columns that should be unique.
• Forgetting normalization. Fix: if the business treats values the same after trimming or lowercasing, apply that in the query before grouping.
• Using COUNT(column) when nulls matter. Fix: prefer COUNT(*) so null rows are counted too.
• Assuming DISTINCT is enough. Fix: DISTINCT removes repeats, but GROUP BY ... HAVING or ROW_NUMBER() is what reveals them.

Memory Hook: Group by counts the crowd; row_number gives each person a seat number. If seat number is greater than 1, that person is a duplicate you need to review.

Cheat Sheet:

• Define duplicate first: business key or full row?
• GROUP BY key HAVING COUNT(*) > 1 finds repeated values.
• ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) finds extra rows.
• Use LOWER(TRIM(...)) when case and spaces should not matter.
• Watch NULL behavior and choose COUNT(*) intentionally.
• Indexes help, but duplicate checks still often scan a lot of data.

Practice Tasks:

• Find duplicate email values in the sample table using only GROUP BY.
• Write a query that returns the second and later rows for each duplicate email using ROW_NUMBER().
• Extend the query to detect duplicate full_name + email combinations instead of email alone.