SQL Duplicate Records: Find Them & Define the Right Key

This guide shows you how to identify duplicate records in a SQL table and, more importantly, how to define the correct primary key or unique constraint so duplicates never happen again. Youâ€™ll learn to spot the difference between true duplicates (every column identical) and near-duplicates (same logical entity with different details), then write queries to find both.

What You’ll Need

Access to a SQL database (MySQL, PostgreSQL, SQL Server, SQLite, etc.)
A table you suspect contains duplicates
Basic understanding of SELECT, GROUP BY, and JOIN syntax
Permission to run SELECT queries (and optionally ALTER TABLE if you plan to add a key)

Step-by-Step

Understand what â€œduplicateâ€� means for your data. A true duplicate is a row where every column value matches another row. A near-duplicate is when the business key (e.g., email, order number, customer ID) repeats but other columns differ. Decide which type you need to findâ€”this determines your query.
Inspect the table structure. Run DESCRIBE table_name; or SELECT * FROM table_name LIMIT 10; to see the columns and sample data. Note which columns should be unique in a perfect worldâ€”these are your candidate keys.
Find exact duplicates. Use GROUP BY on all columns and filter with HAVING COUNT(*) > 1:
```
SELECT col1, col2, ..., COUNT(*) AS cnt
FROM your_table
GROUP BY col1, col2, ...
HAVING COUNT(*) > 1;
```
Replace the column list with every column in the table. This returns groups that appear more than once.
Find duplicates on a business key. If you only care about, say, email addresses repeating, group by that column:
```
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
```
This catches near-duplicates where the email is the same but other fields (name, phone) differ.
List the actual duplicate rows. The GROUP BY query shows you the duplicate keys, but not the rows themselves. To see every duplicate row, join the grouped result back to the original table:
```
SELECT t.*
FROM your_table t
INNER JOIN (
  SELECT col1, col2, ...
  FROM your_table
  GROUP BY col1, col2, ...
  HAVING COUNT(*) > 1
) d ON t.col1 = d.col1 AND t.col2 = d.col2 ...;
```
This gives you all rows that share a duplicate key, so you can inspect them.
Define the right key. Based on what you found, decide which column(s) uniquely identify each row. A good key is:
- Stable (never changes)
- Minimal (no extra columns)
- Always present (no NULLs)
Common choices: an auto-increment ID (surrogate key) or a natural key like email or order number.
Add the key as a PRIMARY KEY or UNIQUE constraint. Once youâ€™ve cleaned the duplicates, enforce the key:
```
ALTER TABLE your_table ADD PRIMARY KEY (id);
-- or
ALTER TABLE your_table ADD CONSTRAINT unique_email UNIQUE (email);
```
This prevents future duplicates. If you have existing duplicates, you must remove them first (see Troubleshooting).

Common Mistakes

Grouping by all columns when you only care about a subset. This misses near-duplicates where the business key repeats but other fields differ.
Forgetting NULLs. In SQL, NULL is not equal to NULL, so GROUP BY treats two NULLs as different. If your key column has NULLs, duplicates may be missed. Use COALESCE(column, '') in the GROUP BY if needed.
Assuming the primary key is the first column. The right key depends on business logic, not column order.
Deleting duplicates without a backup. Always back up the table before running DELETE.

Tips

Use ROW_NUMBER() to identify and delete duplicates in one pass. For example, to keep the lowest ID per duplicate group:

DELETE FROM your_table
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM your_table
  ) t WHERE rn > 1
);

Test your key definition on a copy of the data before applying it to production.
Document why you chose that keyâ€”future developers will thank you.
If youâ€™re unsure, start with a UNIQUE constraint instead of a PRIMARY KEY; you can promote it later.

Troubleshooting

Query returns no duplicates, but you know they exist. Check for trailing spaces or case differences. Use TRIM() and LOWER() in the GROUP BY. Also check for NULLsâ€”they are not grouped together by default.
Adding a primary key fails with â€œDuplicate entryâ€�. You still have duplicate rows. Run the GROUP BY query again to see them, then decide which row to keep. Delete the extras, then retry the ALTER TABLE.
GROUP BY all columns is slow on a huge table. Add an index on the columns you group by, or limit the query to a subset of rows (e.g., WHERE created_at > ‘2024-01-01’) to test first.
You canâ€™t decide between two candidate keys. Look for columns that are never NULL and never change. If both qualify, choose the one with fewer columns or the one that is more stable over time.

Frequently Asked Questions

What is the difference between a primary key and a unique constraint?

A primary key uniquely identifies each row and cannot be NULL. A unique constraint also enforces uniqueness but allows one NULL per column (in most databases). You can have only one primary key per table, but multiple unique constraints.

Can I use GROUP BY to find duplicates on multiple columns?

Yes, list all the columns that together should be unique. For example, GROUP BY first_name, last_name finds rows where both columns repeat together.

How do I delete duplicates but keep one row?

Use a window function like ROW_NUMBER() to assign a rank per duplicate group, then delete rows with rank > 1. Alternatively, use DELETE t1 FROM table t1 INNER JOIN table t2 WHERE t1.id > t2.id AND t1.email = t2.email; (works in MySQL).

What if my table has no natural key?

Add a surrogate key: an auto-increment integer column. This is often the safest choice because itâ€™s guaranteed unique and never changes.

Does defining a primary key automatically remove existing duplicates?

No. You must delete or merge duplicates before adding the key. The database will reject the ALTER TABLE if duplicates exist.

Can I find duplicates in a view?

Yes, the same GROUP BY and JOIN techniques work on views, as long as the view is updatable if you want to delete rows.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →