All Articles

Find Duplicate Rows in SQL: 5 Proven Ways

Query Patterns
#duplicate-rows#data-quality#window-functions#sql-interview#sql-patterns

Why Finding Duplicates Is a Core SQL Skill

Duplicate data is one of the most common data-quality problems in production databases — double-submitted form entries, failed deduplication during a data migration, or a bug that inserted the same event twice. "Find the duplicates" is also one of the most frequently asked SQL interview questions, precisely because there's more than one correct way to solve it, and interviewers want to see which approach you reach for and why.

This guide covers five distinct techniques, from the most common (GROUP BY + HAVING) to window-function and self-join approaches, plus how to actually remove duplicates safely once you've found them.

The Sample Data

-- users table with accidental duplicate emails
| id | email              | created_at          |
|----|--------------------|-----------------------|
| 1  | alice@example.com  | 2026-01-01 09:00:00  |
| 2  | bob@example.com    | 2026-01-01 09:05:00  |
| 3  | alice@example.com  | 2026-01-02 14:00:00  |
| 4  | carol@example.com  | 2026-01-03 10:00:00  |
| 5  | alice@example.com  | 2026-01-04 11:00:00  |

Here, alice@example.com appears three times (ids 1, 3, 5) — that's the duplicate we want every technique below to surface.

Method 1: GROUP BY + HAVING (the classic approach)

The most common and most readable way to find which values are duplicated, and how many times.

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Result:
-- | email             | occurrences |
-- |-------------------|-------------|
-- | alice@example.com | 3           |

This tells you which values are duplicated and how many times, but not which specific rows (with their full column data) are involved — for that, you need to join back to the original table or use one of the methods below.

Method 2: Self Join to Get Full Duplicate Rows

To see the complete duplicate rows (not just the repeated value), join the table to itself on the duplicated column, guarding against a row matching itself.

SELECT u1.*
FROM users u1
JOIN users u2 ON u1.email = u2.email AND u1.id != u2.id
ORDER BY u1.email;

-- Returns all 3 rows for alice@example.com, with full column data

This is useful when you need every column from each duplicate row, but be aware it returns every pairing — with 3 duplicates it effectively cross-matches within the group, so always pair it with DISTINCT or move to Method 3 if you specifically want one row per duplicate.

Method 3: Window Functions (ROW_NUMBER) — the most flexible approach

This is the modern, most versatile method, and the one most interviewers actually want to see for anything beyond a simple count. It assigns a sequential number to each row within a group of duplicates, which lets you both identify duplicates and decide which copy to keep.

SELECT id, email, created_at, rn
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn
  FROM users
) t
WHERE rn > 1;

-- Result: every row EXCEPT the first (earliest) occurrence per email
-- | id | email              | created_at          | rn |
-- |----|--------------------|-----------------------|----|
-- | 3  | alice@example.com  | 2026-01-02 14:00:00  | 2  |
-- | 5  | alice@example.com  | 2026-01-04 11:00:00  | 3  |

The ORDER BY inside PARTITION BY matters a lot here — it decides which copy is treated as the "original" (rn = 1) and which are the "extra" duplicates. Order by created_at ASC to keep the oldest record, or DESC to keep the newest — this decision should always come from a real business rule, not be left arbitrary.

Method 4: EXISTS-Based Duplicate Check

Useful when you just need a yes/no signal — for example, in application code deciding whether to block an insert.

SELECT *
FROM users u1
WHERE EXISTS (
  SELECT 1 FROM users u2
  WHERE u2.email = u1.email AND u2.id != u1.id
);

Functionally similar to Method 2 but often performs better on large tables because EXISTS can short-circuit after finding just one match, rather than joining and multiplying rows.

Method 5: COUNT() as a Window Function (find + flag in one pass)

A hybrid approach: compute the duplicate count per group without collapsing rows, so every original row stays visible alongside its duplicate count.

SELECT *,
  COUNT(*) OVER (PARTITION BY email) AS duplicate_count
FROM users
ORDER BY email;

-- Every row keeps its own line, tagged with how many total copies of its email exist
-- | id | email              | created_at          | duplicate_count |
-- |----|--------------------|-----------------------|-----------------|
-- | 1  | alice@example.com  | 2026-01-01 09:00:00  | 3               |
-- | 3  | alice@example.com  | 2026-01-02 14:00:00  | 3               |
-- | 5  | alice@example.com  | 2026-01-04 11:00:00  | 3               |
-- | 2  | bob@example.com    | 2026-01-01 09:05:00  | 1               |

This is the best method when you need duplicates flagged inline in a report or dashboard, rather than filtered out entirely.

Actually Removing Duplicates (Safely)

Finding duplicates is step one — deleting them safely is where mistakes get expensive. The safe pattern uses the same ROW_NUMBER() approach from Method 3, wrapped in a DELETE:

-- PostgreSQL / SQL Server: DELETE using a CTE
WITH duplicates AS (
  SELECT id,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);

-- MySQL: no DELETE...WITH, use a derived table instead
DELETE u FROM users u
JOIN (
  SELECT id,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn
  FROM users
) ranked ON u.id = ranked.id
WHERE ranked.rn > 1;

Always run the equivalent SELECT first (Method 3) and review the exact rows that would be deleted before running the DELETE — this is a one-way operation without a transaction wrapping it, and "which copy is the original" is a decision worth double-checking against the business requirement, not just the query logic.

Preventing Duplicates Going Forward

  • Add a UNIQUE constraint on the column(s) that should never repeat — this stops the problem at the source instead of cleaning it up after the fact
  • Use INSERT ... ON CONFLICT DO NOTHING (PostgreSQL) or INSERT IGNORE / ON DUPLICATE KEY UPDATE (MySQL) for idempotent inserts
  • Deduplicate at the application layer before insert when the uniqueness rule is complex (e.g. case-insensitive email matching, fuzzy matching)

Common Mistakes

  • Deleting duplicates without deciding which copy to keep first. Always define the "keep" rule (oldest, newest, most complete record) before writing the DELETE.
  • Using Method 2 (self join) on large tables without an index on the join column — this produces a full self-scan and can be extremely slow.
  • Confusing "duplicate values" with "duplicate rows." Two rows can share an email but differ in every other column — decide up front whether you care about exact full-row duplicates or duplicates on a specific key.
  • Forgetting to check case and whitespace — Alice@Example.com and alice@example.com (trailing space) won't match a naive GROUP BY email; normalize with LOWER(TRIM(email)) first if that's a real risk in your data.

Common Interview Questions on This Pattern

  1. Write a query to find duplicate emails and how many times each appears. Method 1 — GROUP BY + HAVING.
  2. Write a query to delete duplicate rows, keeping only the earliest one. Method 3's ROW_NUMBER pattern wrapped in a DELETE, as shown above.
  3. How would you find duplicates across multiple columns (a composite key)? Same techniques, just list every column in GROUP BY / PARTITION BY, e.g. PARTITION BY first_name, last_name, date_of_birth.
  4. What's the difference between COUNT(*) and COUNT(DISTINCT column) here? COUNT(*) counts all rows in a group (including duplicates); COUNT(DISTINCT column) counts unique values — useful for checking "how many actually-unique emails exist" versus total row volume.

Frequently Asked Questions

Which method is fastest on a large table?

GROUP BY + HAVING (Method 1) is typically fastest when you only need counts, since it doesn't need to materialize full duplicate rows. For full-row results, the window function approach (Method 3) generally outperforms the self join (Method 2) because it avoids the row-multiplying join.

Can a UNIQUE constraint retroactively fix existing duplicates?

No — adding a UNIQUE constraint to a column that already has duplicate values will fail. You have to deduplicate the existing data first (using one of the methods above), then add the constraint to prevent future duplicates.

Practice This Pattern

Deduplication questions are a great way to demonstrate you can reason about which rows survive a query, not just which values match. Try it hands-on with our SQL practice questions, or work through a messy real-world dataset in our case studies. For more on window functions like ROW_NUMBER, see our SQL interview questions guide.

Back to All Articles