Как избежать сравнения записи самой с собой при поиске дубликатов в SQL

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

Умеете ли вы работать с самосоединением и понимаете ли, откуда в нём берутся лишние пары. Следом обычно просят оставить по одной записи из каждой группы дублей и удалить остальные, а иногда спрашивают, как вызвать такой запрос из Spring Data.

Как отвечать

Запись сравнивается сама с собой при self join: таблица соединяется с собой по «дублирующим» колонкам, и каждая строка находит пару в себе же. Способы это исключить:

• a.id <> b.id: убирает совпадение строки с собой, но каждая пара дублей попадает в результат дважды, как (1, 2) и (2, 1);
• a.id < b.id: оставляет одну пару из двух и сразу подходит для удаления: удалить b, если есть a с тем же email и меньшим id;
• *`GROUP BY email HAVING COUNT() > 1`**: самосоединение не нужно вовсе, если достаточно узнать, какие значения повторяются;
• ROW_NUMBER() OVER (PARTITION BY email ORDER BY id): нумерует строки внутри группы, строки с номером больше 1 и есть лишние копии. Оконные функции есть в PostgreSQL, MySQL 8, Oracle и SQL Server;
• EXISTS: условие EXISTS (SELECT 1 FROM users b WHERE b.email = a.email AND b.id < a.id) возвращает каждую лишнюю копию ровно один раз, без группировки.

Сравнивать нужно по уникальному ключу. Если первичного ключа нет, в PostgreSQL выручает системная колонка ctid, но это физический адрес строки, а не стабильный идентификатор.

Для Java-разработчика стоит добавить: стандартный JPQL оконных функций не знает, поэтому такие запросы обычно пишут нативными через @Query(nativeQuery = true). А после чистки вешают уникальный индекс, чтобы дубли не появлялись снова.

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

Пишут a.id <> b.id и считают дубли через COUNT(*) по результату соединения. Каждая пара учитывается дважды, а группа из трёх одинаковых строк даёт шесть.

Сравнивают колонки, где бывает NULL. Условие a.email = b.email для двух NULL не истинно, и такие дубли не находятся. Нужен IS NOT DISTINCT FROM в PostgreSQL или <=> в MySQL.

Выгружают таблицу в Java и ищут повторы через HashSet. На миллионах строк это лишний трафик и риск OutOfMemoryError, хотя база сделает то же самое по индексу одним запросом.