Как найти дубли в sql

Для того чтобы найти дубликаты в базе данных SQL, можно использовать операторы SELECT и GROUP BY совместно с оператором HAVING. Например, следующий запрос позволяет найти все строки, у которых значение определенного столбца повторяется более одного раза:

SELECT столбец, COUNT(*) AS количество
FROM таблица
GROUP BY столбец
HAVING COUNT(*) > 1;

Этот запрос выведет все уникальные значения столбца "столбец", а также количество раз, которое каждое значение повторяется в таблице. Результатом будут только те строки, где значение столбца повторяется более одного раза, что указывает на наличие дубликатов.

Также можно использовать операторы JOIN или подзапросы для более сложных запросов на поиск дубликатов. Важно помнить, что перед выполнением подобных операций на поиск дубликатов необходимо создать резервную копию базы данных или таблицы, чтобы избежать потери данных.

Дубли находят группировкой: GROUP BY по полю, которое должно быть уникальным, и HAVING COUNT(*) > 1:

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

Запрос вернёт адреса почты, которые встречаются больше одного раза, и сколько раз каждый.

Пример

Таблица users:

Результат запроса выше:

Дубли по нескольким полям

Перечислите поля в GROUP BY:

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

Показать все строки-дубли целиком

GROUP BY схлопывает строки. Чтобы увидеть каждую строку с её id, используйте оконную функцию:

SELECT id, name, email
FROM (
  SELECT *, COUNT(*) OVER (PARTITION BY email) AS cnt
  FROM users
) t
WHERE cnt > 1
ORDER BY email, id;

Оконные функции есть в PostgreSQL, MySQL 8+, MS SQL Server и SQLite 3.25+.

Удалить дубли, оставив одну строку

Сначала проверьте, что удалится: замените DELETE на SELECT *.

Оставить строку с наименьшим id (PostgreSQL, SQLite):

DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);

В MySQL подзапрос к той же таблице нужно обернуть ещё раз, иначе будет ошибка «You can't specify target table»:

DELETE FROM users
WHERE id NOT IN (
  SELECT id FROM (SELECT MIN(id) AS id FROM users GROUP BY email) AS keep
);

В MS SQL Server — через ROW_NUMBER:

WITH numbered AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
)
DELETE FROM numbered WHERE rn > 1;

Одинаковые строки в двух таблицах

SELECT a.email
FROM users a
JOIN subscribers b ON b.email = a.email;

Чтобы дубли не появлялись

Добавьте ограничение уникальности. База не даст вставить повторяющееся значение:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Перед этим удалите существующие дубли, иначе команда не выполнится.

GROUP BY, HAVING и оконные функции разбирают в бесплатном курсе «Основы SQL» и на курсе «SQL для программистов».