How Do You Find Duplicates in a Table?

Group by the columns that should be unique, and ask for the groups that have more than one row. The key is to know which columns define the duplicate — a duplicate row is only a duplicate with respect to some key.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email VARCHAR(200) NOT NULL,
  full_name VARCHAR(120) NOT NULL
);

INSERT INTO accounts (id, email, full_name) VALUES
  (1, '[email protected]',   'Ada Lovelace'),
  (2, '[email protected]',   'Ada Lovelace'),
  (3, '[email protected]', 'Grace Hopper'),
  (4, '[email protected]',   'Ana Lopez');

SELECT email,
       COUNT(*) AS row_count,
       GROUP_CONCAT(id) AS duplicate_ids
FROM accounts
GROUP BY email
HAVING COUNT(*) > 1;

One row comes back: [email protected] with a count of 2 and the ids 1 and 2.

The shape of the query is the whole thing. GROUP BY email collapses the rows into groups by address, COUNT(*) counts each group, and HAVING keeps only the groups with more than one row. That is different from filtering in WHERE, which would remove rows before grouping and leave nothing to count.

What you get back is a summary, not the offending rows themselves. To see the full rows, you have to go back for them — either a self-join on the duplicated key, or a window function that counts each row's group and then filters on that count. The window version is usually the clearer one, because it produces the full row and lets you see which fields differ between the duplicates.

Three things to be careful about. If the duplicate key is nullable, NULLs group together and will show up as a duplicate group even though each NULL row may be legitimate. If you are looking for rows that are identical in every column, use GROUP BY across all the columns rather than just the business key. And the fastest way to check whether a table should have duplicates at all is to look for a UNIQUE constraint that is missing — if the database enforces the rule, the query is only ever a diagnostic.

Group by the business key, filter with HAVING, and join the group ids back so you can see the actual rows involved.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email VARCHAR(200) NOT NULL,
  full_name VARCHAR(120) NOT NULL
);

CREATE TABLE signups (
  id INTEGER PRIMARY KEY,
  account_id INTEGER,
  email VARCHAR(200) NOT NULL
);

INSERT INTO accounts (id, email, full_name) VALUES
  (1, '[email protected]',   'Ada Lovelace'),
  (2, '[email protected]', 'Grace Hopper');

INSERT INTO signups (id, account_id, email) VALUES
  (1, 1, '[email protected]'),
  (2, 1, '[email protected]'),
  (3, 2, '[email protected]'),
  (4, NULL, '[email protected]');

SELECT email,
       COUNT(*) AS signup_count,
       GROUP_CONCAT(id) AS signup_ids
FROM signups
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

One row comes back: [email protected] with a count of 2 and the signup ids 1 and 2.

GROUP_CONCAT is the convenience here — it collects the ids in the group into one comma-separated string, which is exactly what you want to paste into a follow-up query or to put in a ticket. It is not standard SQL, though: SQLite and MySQL have it, and PostgreSQL needs string_agg instead. If you need it to be portable, collect the ids in a subquery and let the application join them.

Note that the duplicate here is a duplicate signup, not a duplicate account — the account table has one row per email, and the signups table has two rows pointing at it. That distinction matters for what you do next: the fix might be to delete the redundant signup, not to merge accounts.

Two variants worth knowing. To find rows that are duplicated on one key but differ elsewhere — which is usually a sign of two different real records rather than a data-entry mistake — group by the key and count the DISTINCT values of another column. And to find rows that are identical in every column, group by all of them. The first finds near-duplicates, the second finds exact copies, and they call for different remedies.

A window function numbers the rows within each duplicate group, and the outer query keeps the ones numbered one.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email VARCHAR(200) NOT NULL,
  full_name VARCHAR(120) NOT NULL
);

INSERT INTO accounts (id, email, full_name) VALUES
  (1, '[email protected]',   'Ada Lovelace'),
  (2, '[email protected]',   'Ada Lovelace'),
  (3, '[email protected]', 'Grace Hopper'),
  (4, '[email protected]',   'Ana Lopez');

-- Find the duplicates.
SELECT email, COUNT(*) AS row_count
FROM accounts
GROUP BY email
HAVING COUNT(*) > 1;

-- Keep the lowest id in each duplicate group.
DELETE FROM accounts
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM accounts
  ) AS numbered
  WHERE numbered.rn > 1
);

SELECT id, email FROM accounts ORDER BY id;

The first query reports one duplicate group — [email protected] with a count of 2. After the delete, three rows remain: id 1 with [email protected], id 3 with [email protected] and id 4 with [email protected]. Ada's duplicate, id 2, is gone and the original id 1 was kept.

The delete is written as a DELETE with a subquery selecting the ids to remove, which is the pattern for this cleanup on every engine. The subquery numbers the rows within each email group by id, and the outer DELETE removes everything numbered above one. The extra layer of nesting — the SELECT inside the SELECT inside the FROM — is there because some engines do not allow a window function to be referenced directly in a subquery's WHERE clause.

Three decisions to make before you run a cleanup like this. Which row to keep — the lowest id, the most recent, or the one with the most complete data? The rule belongs in the ORDER BY inside the window, and it has to be deterministic. Whether to run it in a transaction and check the affected-row count before committing, which is the habit that makes a destructive cleanup recoverable. And whether to delete at all — very often the right fix is a UNIQUE constraint on the column plus an application-level upsert, so the duplicates cannot come back, and the cleanup is a one-off.