Можно ли использовать self join для поиска дубликатов в таблице

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

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

Как отвечать

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

SELECT DISTINCT a.* FROM users a JOIN users b ON a.email = b.email AND a.id > b.id;

Условие a.id > b.id вместо a.id <> b.id важно: с <> каждая пара выводится дважды, а с > остаются ровно те строки, у которых есть более ранний «оригинал». Это готовый список на удаление. В PostgreSQL удаление пишется так: DELETE FROM users a USING users b WHERE a.email = b.email AND a.id > b.id, в MySQL через DELETE a FROM users a JOIN users b ON ....

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

• *`GROUP BY ... HAVING COUNT() > 1`**: быстрый ответ на вопрос «какие значения дублируются и сколько раз», но без id конкретных строк;
• оконная функция: ROW_NUMBER() OVER (PARTITION BY email ORDER BY id), всё с номером больше 1 и есть дубли. Один проход по таблице и гибкий выбор, какую запись оставить;
• EXISTS: коррелированный подзапрос, часто даёт тот же план, что self join, но читается проще.

Для Java-разработчика стоит закончить профилактикой: после чистки ставится UNIQUE-ограничение, а в Spring его нарушение приходит как DataIntegrityViolationException. Проверка «сначала existsBy..., потом save» от дублей не спасает: две параллельные транзакции обе увидят, что записи нет.

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

Пишут a.id <> b.id и получают каждую пару дважды, а при удалении по такому списку сносят все копии, включая оригинал.

Забывают про NULL. Условие a.phone = b.phone для двух пустых телефонов не выполняется, такие дубли запрос не увидит. В PostgreSQL для этого есть IS NOT DISTINCT FROM.

Не думают об объёме. Группа из 1000 одинаковых строк даёт почти полмиллиона пар, а без индекса по колонкам сравнения соединение становится тяжёлым. На больших таблицах оконная функция обычно дешевле.