How to Find Duplicate Records in a SQL Table?

Use GROUP BY and HAVING to detect duplicates, then inspect and clean them up safely

Duplicate rows are one of the most common data quality problems. They creep in through repeated imports, missing constraints, or application bugs, and they quietly distort reports, inflate counts, and break joins. Finding them is also a classic SQL interview question. In this post, we'll look at the simplest way to detect duplicates, the pitfalls to watch for, and a few variations for inspecting and cleaning them up.

The core query

Suppose you have an employees table and want to know which names appear more than once.

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

This returns each name that appears more than once, along with how many times it appears.

How it works

1. GROUP BY name collapses all rows with the same name into a single group.
2. COUNT(*) counts how many rows each group contains.
3. HAVING COUNT(*) > 1 keeps only the groups with more than one row.

Why HAVING and not WHERE?

WHERE filters individual rows before grouping happens, so it cannot see the result of COUNT(*). HAVING filters groups after aggregation, which is exactly what we need here.

Pitfalls to watch for

What counts as a duplicate?

Two employees can share a name without being duplicate records. Before you act on the results, decide which columns define uniqueness. For true duplicates, group by every column that should be unique, such as name and email.

NULL values group together

If several rows have a NULL name, they will show up as a single duplicate group. Decide whether that is meaningful for your data or whether you should exclude them with WHERE name IS NOT NULL.

Case and whitespace matter

In most databases, "Asha" and "asha " are different values, so near-duplicates slip through. Normalize the column first, for example with LOWER(TRIM(name)), to catch them.

Make the output readable

Give the count an alias, such as COUNT(*) AS duplicate_count, so the result is easier to read and sort.

Variation 1: Duplicates across multiple columns

When a single column isn't enough, group by several.

SELECT name, email, COUNT(*) AS duplicate_count
FROM employees
GROUP BY name, email
HAVING COUNT(*) > 1;

Only rows that match on both name and email are reported, which is a much stronger signal of a true duplicate.

Variation 2: See the full duplicate rows

The grouped query shows counts, but often you want to look at the actual rows. Join the results back to the table.

SELECT e.*
FROM employees e
JOIN (
  SELECT name
  FROM employees
  GROUP BY name
  HAVING COUNT(*) > 1
) d ON e.name = d.name;

Now you can compare IDs, dates, and other columns to decide which record to keep.

Variation 3: Flag the extra copies with ROW_NUMBER

A window function can number the rows within each group of duplicates, so every copy after the first stands out.

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
  FROM employees
) t
WHERE rn > 1;

Here, PARTITION BY name restarts the numbering for each name, and ORDER BY id keeps the earliest record as number 1. Any row with rn > 1 is an extra copy. This pattern is especially useful when you want to delete all but one record.

Which approach should you choose?

Goal	Best approach
Detect which values are duplicated	GROUP BY with HAVING
Inspect the full duplicate rows	Join back to the grouped subquery
Remove all but one copy	ROW_NUMBER with PARTITION BY

A word of caution before deleting

Never delete duplicates blindly. Run the SELECT version first and review exactly which rows would be affected. If possible, take a backup or work inside a transaction so you can roll back. Once the data is clean, prevent the problem from returning by adding a UNIQUE constraint or a unique index on the columns that should never repeat.

Final thoughts

The GROUP BY ... HAVING COUNT(*) > 1 pattern is the simplest and most portable way to find duplicates, and it works on virtually every SQL database. Start there to detect the problem, switch to a join when you need to inspect the rows, and use ROW_NUMBER when it's time to clean up. Combine that with a unique constraint, and you'll keep your tables tidy for the long run.