Как найти повторяющиеся записи в таблице по полю без уникального ограничения

Что проверяют

Базовое владение SQL: группировка, HAVING, оконные функции. Настоящий вопрос обычно следующий: как вывести сами строки-дубли целиком и как удалить лишние, оставив по одной.

Как отвечать

Начните с классики: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. WHERE фильтрует строки до группировки, HAVING фильтрует группы после неё, поэтому условие на агрегат пишется только в HAVING.

Дальше покажите, как получить полные строки:

• оконная функция: COUNT(*) OVER (PARTITION BY email) в подзапросе, затем фильтр cnt > 1. Один проход, видны все колонки;
• нумерация: ROW_NUMBER() OVER (PARTITION BY email ORDER BY id), строки с номером больше 1 это лишние копии. На этом строится удаление: DELETE FROM users WHERE id IN (SELECT id FROM (...) t WHERE rn > 1);
• EXISTS: строка, для которой есть другая с тем же email и меньшим id.

Уточните, что считать дублем. NULL в GROUP BY собирается в одну группу, хотя сравнение NULL = NULL не даёт истину. Адреса Ivan@Example.com и ivan@example.com для бизнеса одинаковые, поэтому группировать стоит по lower(trim(email)).

Закончите профилактикой: после чистки добавьте ограничение UNIQUE (в PostgreSQL для регистронезависимости уникальный индекс по lower(email)), иначе дубли вернутся. В Java-проекте это миграция Flyway или Liquibase. Аннотация @Column(unique = true) в JPA сама на проде ничего не создаёт, она влияет только на генерацию схемы.

Где спотыкаются

Пишут WHERE COUNT(*) > 1. Агрегат в WHERE недопустим, запрос не выполнится.

Удаляют дубли без явного правила, какую копию оставить, и без транзакции, в итоге стирают все копии вместе с оригиналом. Сначала SELECT с тем же условием, потом DELETE.

Ищут дубли кодом на Java: вытаскивают всю таблицу в List и сравнивают через HashMap. На миллионах строк это память и минуты там, где база справится одним запросом.