Простой запрос на удаление дубликатов из таблицы

В этой небольшой статье я хочу поделиться простым способом удаления дубликатов из таблицы. Запрос работает в базах данных MySQL, MariaDB и PostgreSQL. Если вам интересен такой запрос для других СУБД, напишите мне в комментариях.

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

create table t (
    id int primary key,
    v int
);

insert into t(id, v) values 
(1, 1),(2, 1),(3, 2),(4, 2),(5, 1),(6, 1),
(7, 2),(8, 3),(9, 2),(10, 4),(11, 3);

Приведенный выше код создает таблицу и вставляет несколько значений. Как видите, id имеет уникальные значения, но поле v имеет несколько дубликатов:

+====+===+
| id | v |
+====+===+
| 1  | 1 |
| 2  | 1 |
| 3  | 2 |
| 4  | 2 |
| 5  | 1 |
| 6  | 1 |
| 7  | 2 |
| 8  | 3 |
| 9  | 2 |
| 10 | 4 |
| 11 | 3 |
+----+---+

Наша миссия состоит в том, чтобы удалить эти повторяющиеся строки в столбце v и сохранить уникальные значения с минимальным значением идентификатора.
Как мы можем найти дубликаты? Мы можем использовать простое LEFT JOIN для поля v с дополнительным условием для предотвращения объединения строк:

select * 
from t
left join t t1 on t.v = t1.v and t.id > t1.id;

Запрос дает нам следующий результат:

+====+===+========+========+
| id | v | id     | v      |
+====+===+========+========+
| 1  | 1 | (null) | (null) |
| 2  | 1 | 1      | 1      |
| 3  | 2 | (null) | (null) |
| 4  | 2 | 3      | 2      |
| 5  | 1 | 1      | 1      |
| 5  | 1 | 2      | 1      |
| 6  | 1 | 1      | 1      |
| 6  | 1 | 2      | 1      |
| 6  | 1 | 5      | 1      |
| 7  | 2 | 3      | 2      |
| 7  | 2 | 4      | 2      |
| 8  | 3 | (null) | (null) |
| 9  | 2 | 3      | 2      |
| 9  | 2 | 4      | 2      |
| 9  | 2 | 7      | 2      |
| 10 | 4 | (null) | (null) |
| 11 | 3 | 8      | 3      |
+----+---+--------+--------+

Мы видим, что уникальные строки с минимальным идентификатором имеют (нулевые) значения в последних столбцах. Итак, нам нужно удалить остальные. Мы можем сделать это в следующем простом запросе:

delete t.*
from t
left join t t1 on t.v = t1.v and t.id > t1.id
where t1.id is not null;

Просто запустите на SQLize.online и посмотрите результат

P.S. После этой публикации мой коллега @Akina предложил более короткую версию:

delete t.*
from t
join t t1 on t.v = t1.v and t.id > t1.id;