How to check duplicate rows in sql

Group by the columns that identify one real thing and keep the groups holding more than one row: SELECT email, count(*) FROM contact GROUP BY email HAVING count(*) > 1. Against the fixture below that returns two groups. Grouping on a normalised key instead returns three groups and eight rows.

Why check this

Two questions hide inside one word. The first is whether rows repeat a key the database was supposed to protect, which happens after an import, a retried job, or a migration that ran twice. The second is whether rows repeat a thing in the business, which no constraint ever covered because those columns were never declared a key.

Run the first before adding a unique index, because those rows will block the migration. Run the second whenever a support ticket says a customer appears twice, or a report counts more orders than were placed.

The failure this prevents is a count nobody can reconcile. Marketing sends the same message four times to one person, and every query that joins through that table multiplies its rows. Four of the rows below look like separate people to SQL and like one person to a reader.

Prerequisites

• sqlite3 3.50.6, 32-bit. The binary on PATH here is the Android SDK platform-tools build at /c/Android/sdk/platform-tools/sqlite3.exe, not a sqlite.org release. Steps 6 and 7 show the difference is not cosmetic. Run which sqlite3 first.
• Node 22.23.2, whose bundled node:sqlite links SQLite 3.51.3, as the second client.
• A restored copy of the table. Grouped counts scan, so run them off the live instance.
• Everything here ran against SQLite. The grouped counts port unchanged; the collation results in steps 5 to 7 do not.
• The fixture used below.

CREATE TABLE contact (
  id    INTEGER PRIMARY KEY,
  email TEXT,
  name  TEXT,
  city  TEXT
);
INSERT INTO contact (id, email, name, city) VALUES
  (1, 'ana@example.com',  'Ana',  'Kyiv'),
  (2, 'ana@example.com',  'Ana',  'Kyiv'),
  (3, 'Ana@Example.com',  'Ana',  'Kyiv'),
  (4, 'ana@example.com ', 'Ana',  'Lviv'),
  (5, 'bo@example.com',   'Bo',   'Gdansk'),
  (6, 'kai@example.com',  'Kai',  'Berlin'),
  (7, 'KAI@example.com',  'Kai',  'Berlin'),
  (8, 'ana@example.de',   'Äna',  'Wien'),
  (9, 'ana@example.de',   'äna',  'Wien');

Save it as dup.sql and build with sqlite3 dup.db < dup.sql. Write the file in an editor or a quoted heredoc, never as shell arguments, or the accented names arrive as ASCII. Steps 6 and 7 need likecheck.sql and likecheck.mjs, holding the same four expressions.

.mode box
SELECT ('Äna' LIKE 'äna') AS like_pair,
       ('Äna' = 'äna' COLLATE NOCASE) AS nocase_pair,
       lower('ÄNA') AS lowered,
       hex('Äna') AS bytes;

import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync('dup.db');
const r = db.prepare(`SELECT ('Äna' LIKE 'äna') AS like_pair,
       ('Äna' = 'äna' COLLATE NOCASE) AS nocase_pair,
       lower('ÄNA') AS lowered,
       hex('Äna') AS bytes`).get();
for (const [k, v] of Object.entries(r)) console.log(k, '=', v);
db.close();

Steps

1. 1

   Step 1.

   Count repeats of the single column you expect to be a key.

   sqlite3 dup.db ".mode box" "SELECT email, count(*) AS n FROM contact GROUP BY email HAVING count(*) > 1;"
   

   ┌─────────────────┬───┐
   │      email      │ n │
   ├─────────────────┼───┤
   │ ana@example.com │ 2 │
   │ ana@example.de  │ 2 │
   └─────────────────┴───┘

   Two keys repeat, four rows in total. These are the rows a unique index on email would reject.

2. 2

   Step 2.

   Group on the columns that identify one real contact instead.

   sqlite3 dup.db ".mode box" "SELECT email, name, city, count(*) AS n FROM contact GROUP BY email, name, city HAVING count(*) > 1;"
   

   ┌─────────────────┬──────┬──────┬───┐
   │      email      │ name │ city │ n │
   ├─────────────────┼──────┼──────┼───┤
   │ ana@example.com │ Ana  │ Kyiv │ 2 │
   └─────────────────┴──────┴──────┴───┘

   One group, down from two. Widening the key always finds fewer duplicates, never more. Rows 8 and 9 dropped out because their names differ in case.

3. 3

   Step 3.

   Group on a normalised key and list the ids behind every group.

   sqlite3 dup.db ".mode box" "SELECT lower(trim(email)) AS k, count(*) AS n, group_concat(id) AS ids FROM contact GROUP BY k HAVING count(*) > 1;"
   

   ┌─────────────────┬───┬─────────┐
   │        k        │ n │   ids   │
   ├─────────────────┼───┼─────────┤
   │ ana@example.com │ 4 │ 1,2,3,4 │
   │ ana@example.de  │ 2 │ 8,9     │
   │ kai@example.com │ 2 │ 6,7     │
   └─────────────────┴───┴─────────┘

   Three groups, eight rows. group_concat(id) turns the report into a work list.

4. 4

   Step 4.

   Look at the raw bytes of three rows that step 1 called distinct.

   sqlite3 dup.db ".mode box" "SELECT id, quote(email) AS stored, length(email) AS len FROM contact WHERE id IN (1,3,4);"
   

   ┌────┬────────────────────┬─────┐
   │ id │       stored       │ len │
   ├────┼────────────────────┼─────┤
   │ 1  │ 'ana@example.com'  │ 15  │
   │ 3  │ 'Ana@Example.com'  │ 15  │
   │ 4  │ 'ana@example.com ' │ 16  │
   └────┴────────────────────┴─────┘

   Row 4 is 16 characters against 15. The trailing space shows only because quote() puts a delimiter after it and length() counts it. A plain SELECT pads the column and the difference disappears.

5. 5

   Step 5.

   Group the names with a case-insensitive collation and check which pairs it misses.

   sqlite3 dup.db ".mode box" "SELECT name, count(*) AS n, group_concat(id) AS ids FROM contact GROUP BY name COLLATE NOCASE HAVING count(*) > 1;"
   

   ┌──────┬───┬─────────┐
   │ name │ n │   ids   │
   ├──────┼───┼─────────┤
   │ Ana  │ 4 │ 1,2,3,4 │
   │ Kai  │ 2 │ 6,7     │
   └──────┴───┴─────────┘

   NOCASE folded KAI onto Kai. It did not fold Äna onto äna, so ids 8 and 9 are absent. The documented rule is that NOCASE covers the 26 ASCII letters and nothing else. Whether lower() and LIKE keep that limit turns out to depend on the build.

6. 6

   Step 6.

   Probe the comparison rules of the client you are using, reading the statement from a file.

   sqlite3 dup.db < likecheck.sql
   

   ┌───────────┬─────────────┬─────────┬──────────┐
   │ like_pair │ nocase_pair │ lowered │  bytes   │
   ├───────────┼─────────────┼─────────┼──────────┤
   │ 1         │ 0           │ äna     │ C3846E61 │
   └───────────┴─────────────┴─────────┴──────────┘

   bytes is C3846E61, so the literal reached the engine intact and the other columns are about comparison, not encoding. This build folds Ä under LIKE and lower(), and does not fold it under NOCASE.

7. 7

   Step 7.

   Run the same four expressions through a second client on the same database file.

   node --no-warnings likecheck.mjs
   

   like_pair = 0
   nocase_pair = 0
   lowered = Äna
   bytes = C3846E61

   Identical bytes, two of four answers reversed. LIKE and lower() are ASCII-only here, as the SQLite documentation describes. The duplicate report depends on which binary ran it.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | No rows from the grouped count | No duplicates under that exact key | State the key you used. The answer is only true for those columns. | | Fewer groups after adding columns | The extra columns differ between near-identical rows | Decide whether that difference identifies a different thing or is noise. | | More rows after normalising | Case or whitespace variants exist | Clean on write, then add the unique index over the normalised expression. | | length() above the visible character count | Trailing or leading whitespace | Trim on import. A plain SELECT will never show this. | | A NOCASE group that misses an obvious pair | The pair differs outside ASCII | Normalise in the application, where full Unicode case folding exists. | | hex() returning ASCII for a non-ASCII literal | The shell transcoded the argument | Put the query in a .sql file and pipe it in. | | LIKE and NOCASE disagreeing on one pair | The build folds Unicode in one and not the other | Compare with = and an explicit COLLATE. Never detect duplicates with LIKE. | | Two clients, two duplicate counts | They link different SQLite builds | Name the binary in the report, next to the number. |

Common mistakes

What to check next

• How to test unique constraint: stops the next duplicate once the current ones are cleaned.
• How to check database collation: which comparison rule decides what counts as the same string.
• How to verify row counts after migration: catches the copy that ran twice, which is where most duplicates come from.
• How to check database integrity: why a file that passes every engine check can still hold rows that mean nothing.

FAQ

How do I find duplicate rows in SQL?

Group by the columns that identify one thing and filter the groups: GROUP BY a, b HAVING count(*) > 1. Add group_concat(id) to get the row ids in each group. Widening the column list always returns fewer groups.

How do I list the offending rows, not the counts?

Filter on the key list: WHERE lower(trim(email)) IN (SELECT lower(trim(email)) FROM contact GROUP BY 1 HAVING count(*) > 1). That returns every member of every duplicate group.

Why does the same query find different duplicates on another engine?

Comparison depends on the collation, and on the build. SQLite NOCASE covers ASCII only, PostgreSQL compares byte by byte under the C collation, and MySQL's default utf8mb4_0900_ai_ci ignores case and accents. Two SQLite clients here disagreed on LIKE against one file.

Should duplicates be removed or merged?

Decide per group, not per table. Rows that differ only in whitespace can be merged. Rows that share a key and differ in a real field, such as ids 1 and 4 above with different cities, are a data question for the owner of the table.

Verified