SQL Remove Duplicates Guide

January 7, 2026 By Matthew Rathbone

Removing duplicates in SQL means one of two things: hiding repeated rows from a query’s result with DISTINCT, or deleting the extra copies from the table so that one of each is left. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.

How to remove duplicates in SQL

SELECT DISTINCT returns each combination of the listed columns once and leaves the table alone. To clean up the table itself, delete every row except one in each group of duplicates, usually the one with the lowest id:

SELECT DISTINCT column_list FROM table_name;

DELETE FROM table_name
WHERE id NOT IN (
  SELECT id FROM (
    SELECT MIN(id) AS id FROM table_name GROUP BY column_list
  ) AS keepers
);

Here Maria Lopez was entered three times:

-- Input: customers
-- id | name        | email             | phone
-- ---+-------------+-------------------+---------
-- 1  | Maria Lopez | maria@example.com | 555-0142
-- 2  | Sam Patel   | sam@example.com   | 555-0178
-- 3  | Maria Lopez | maria@example.com | 555-0142
-- 4  | Lena Novak  | lena@example.com  | 555-0121
-- 5  | Maria Lopez | maria@example.com | 555-0142

SELECT DISTINCT name, email, phone
FROM customers
ORDER BY name;

-- Output:
-- name        | email             | phone
-- ------------+-------------------+---------
-- Lena Novak  | lena@example.com  | 555-0121
-- Maria Lopez | maria@example.com | 555-0142
-- Sam Patel   | sam@example.com   | 555-0178

DELETE FROM customers
WHERE id NOT IN (
  SELECT id FROM (
    SELECT MIN(id) AS id FROM customers GROUP BY name, email, phone
  ) AS keepers
);

-- Output (customers after the DELETE):
-- id | name        | email             | phone
-- ---+-------------+-------------------+---------
-- 1  | Maria Lopez | maria@example.com | 555-0142
-- 2  | Sam Patel   | sam@example.com   | 555-0178
-- 4  | Lena Novak  | lena@example.com  | 555-0121

Rules to know

List the columns that make two rows duplicates. Leave out the id and anything else that’s always different, such as a created_at timestamp. Rows that differ in any listed column are all kept.
The SELECT id FROM (...) AS keepers wrapper is for MySQL, which otherwise refuses to let a DELETE read its own table in a subquery: “You can’t specify target table ‘customers’ for update in FROM clause”.
Look before you delete. Run the GROUP BY or ROW_NUMBER() query first to see what will go, and do the delete inside a transaction you can roll back.
Stop new duplicates with a unique index once the table is clean:

CREATE UNIQUE INDEX customers_email_key ON customers (email);

INSERT INTO customers VALUES (6, 'Maria Lopez', 'maria@example.com', '555-0142');
-- ERROR: duplicate key value violates unique constraint "customers_email_key"

Alternative syntax

In SQL Server code you’ll often see the copies numbered in a CTE and deleted through it, which only SQL Server allows. On the original customers table:

-- SQL Server
WITH numbered AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY name, email, phone ORDER BY id
  ) AS row_num
  FROM customers
)
DELETE FROM numbered WHERE row_num > 1;

-- Output (customers after the DELETE):
-- id | name        | email             | phone
-- ---+-------------+-------------------+---------
-- 1  | Maria Lopez | maria@example.com | 555-0142
-- 2  | Sam Patel   | sam@example.com   | 555-0178
-- 4  | Lena Novak  | lena@example.com  | 555-0121

To combine two queries without repeating rows, use UNION, which removes duplicates, rather than UNION ALL, which keeps them:

SELECT 'apple' AS fruit UNION ALL SELECT 'apple';

-- Output:
-- fruit
-- -----
-- apple
-- apple

SELECT 'apple' AS fruit UNION SELECT 'apple';
-- Output: apple

When should you remove duplicates?

Sometimes you only need to hide repeats in a result, and sometimes the table itself needs cleaning. These are the usual steps, from looking to deleting.

Show each value once

For a report, a dropdown or a quick check of what’s in a column, SELECT DISTINCT drops the repeats from the result and leaves the table as it is.

Count the copies before you delete

Group by the columns that define a duplicate and keep the groups with HAVING COUNT(*) > 1. That shows what’s duplicated and how many times, before anything is removed. The SQL find duplicates guide covers more ways to look.

Choose which copy to keep

ROW_NUMBER() numbers the rows in each group of duplicates. The ORDER BY inside OVER (...) decides which row gets 1 and stays: the oldest, the newest or the most complete.

Delete the extra copies

Once you know which row to keep, delete the others. To keep the newest row instead of the oldest, number the rows by date, newest first, and delete everything numbered above 1.

Real examples of removing duplicates

Every country and carrier you ship with

DISTINCT applies to the whole row of selected columns, so Canada appears once for each carrier:

-- Input: shipments
-- order_id | country | carrier
-- ---------+---------+--------
-- 1001     | Canada  | UPS
-- 1002     | Germany | DHL
-- 1003     | Canada  | FedEx
-- 1004     | Canada  | UPS
-- 1005     | Germany | DHL

SELECT DISTINCT country, carrier
FROM shipments
ORDER BY country, carrier;

-- Output:
-- country | carrier
-- --------+--------
-- Canada  | FedEx
-- Canada  | UPS
-- Germany | DHL

Email addresses that differ only in case

Group by the lower-case email so Jon@Example.com and jon@example.com count as copies. (MySQL and SQL Server already ignore case by default, and LOWER() does no harm there.)

-- Input: accounts
-- id | email
-- ---+----------------------
-- 1  | Ana.Silva@example.com
-- 2  | jon@example.com
-- 3  | ana.silva@example.com
-- 4  | Jon@Example.com
-- 5  | mei@example.com

SELECT LOWER(email) AS email, COUNT(*) AS copies
FROM accounts
GROUP BY LOWER(email)
HAVING COUNT(*) > 1
ORDER BY email;

-- Output:
-- email                 | copies
-- ----------------------+-------
-- ana.silva@example.com | 2
-- jon@example.com       | 2

Keep the copy that has a phone number

Sort the rows with a phone first, then by id, so the most complete row in each group is number 1:

-- Input: contacts
-- id | email            | phone
-- ---+------------------+---------
-- 1  | ravi@example.com | NULL
-- 2  | ravi@example.com | 555-0110
-- 3  | ines@example.com | 555-0133
-- 4  | ines@example.com | NULL

SELECT
  id,
  email,
  phone,
  ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY CASE WHEN phone IS NULL THEN 1 ELSE 0 END, id
  ) AS row_num
FROM contacts
ORDER BY email, row_num;

-- Output:
-- id | email            | phone    | row_num
-- ---+------------------+----------+--------
-- 3  | ines@example.com | 555-0133 | 1
-- 4  | ines@example.com | NULL     | 2
-- 2  | ravi@example.com | 555-0110 | 1
-- 1  | ravi@example.com | NULL     | 2

Deleting the rows numbered 2 or more (ids 1 and 4) leaves one complete contact per email.

Delete exact copies from a table with no id

When the copies are identical in every column, there’s no id to keep the lowest of. Copy the distinct rows into a new table and swap it in. Add back any indexes and constraints the old table had, because the copy doesn’t keep them:

-- Input: page_views
-- url      | viewed_at
-- ---------+--------------------
-- /pricing | 2026-10-01 09:00:00
-- /pricing | 2026-10-01 09:00:00
-- /docs    | 2026-10-01 09:05:00
-- /pricing | 2026-10-01 09:00:00

-- In SQL Server, use SELECT DISTINCT * INTO page_views_clean FROM page_views,
-- then EXEC sp_rename 'page_views_clean', 'page_views'
CREATE TABLE page_views_clean AS SELECT DISTINCT * FROM page_views;
DROP TABLE page_views;
ALTER TABLE page_views_clean RENAME TO page_views;

-- Output (page_views afterwards):
-- url      | viewed_at
-- ---------+--------------------
-- /pricing | 2026-10-01 09:00:00
-- /docs    | 2026-10-01 09:05:00