DELETE ... USING in SQL: JOIN-Style Deletes Without a Subquery

В PostgreSQL можно удалять строки из одной таблицы, используя условия из другой таблицы.

Например:

• удалить заказы пользователей из конкретной страны;
• удалить записи, которые есть в staging-таблице;
• удалить товары из заблокированных категорий;
• удалить события пользователей из blacklist;
• удалить строки, связанные с неактивными аккаунтами.

Для этого есть синтаксис:

DELETE FROM target_table
USING other_table
WHERE target_table.id = other_table.target_id;

Ключевое слово USING позволяет подключить дополнительную таблицу к DELETE.

По смыслу это похоже на JOIN, только в PostgreSQL связь пишется не через ON, а через WHERE.

Базовая идея

Допустим, есть таблица orders:

id  | user_id | amount | status
----+---------+--------+--------
101 | 1       | 1000   | paid
102 | 2       | 1500   | paid
103 | 3       | 700    | pending

И таблица users:

id | email          | country
---+----------------+--------
1  | ann@mail.com   | US
2  | bob@mail.com   | DE
3  | kate@mail.com  | US

Нужно удалить все заказы пользователей из США.

Удаляем из orders, но фильтруем по users.country.

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Что здесь происходит:

DELETE FROM orders o

говорит, что удаляем строки из таблицы orders.

USING users u

подключает таблицу users для фильтрации.

WHERE o.user_id = u.id

связывает заказ с пользователем.

AND u.country = 'US'

оставляет только пользователей из США.

Удалятся заказы пользователей 1 и 3.

Таблица users при этом не изменится. Она используется только как источник условий.

DELETE ... USING — это не удаление из двух таблиц

Очень важно:

DELETE FROM orders o
USING users u
...

удаляет строки только из orders.

Таблица users просто участвует в фильтрации.

То есть USING не означает:

удаляй ещё и из users

Оно означает:

используй users, чтобы понять, какие строки orders удалить

Если нужно удалить данные из нескольких таблиц, это уже другая задача. Обычно её решают через внешние ключи с ON DELETE CASCADE, несколько отдельных DELETE в транзакции или другую архитектуру удаления.

Синтаксис

Общий вид:

DELETE FROM target_table AS t
USING other_table AS o
WHERE t.some_id = o.id
  AND o.some_condition = true;

Где:

• target_table — таблица, из которой реально удаляем строки;
• USING other_table — таблица, по которой фильтруем;
• WHERE — условия связи и дополнительные фильтры.

В отличие от обычного JOIN, здесь нет отдельного ON.

Связь между таблицами пишется прямо в WHERE.

Обычный JOIN:

SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.country = 'US';

DELETE ... USING:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Логика связи та же, просто синтаксис другой.

Алиасы делают запрос понятнее

Лучше почти всегда использовать алиасы.

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Здесь:

• o — alias для orders;
• u — alias для users.

Без алиасов запрос был бы длиннее:

DELETE FROM orders
USING users
WHERE orders.user_id = users.id
  AND users.country = 'US';

А если в таблицах есть одинаковые имена колонок, например id, created_at, status, алиасы помогают избежать неоднозначности.

Как сначала проверить, что удалится

Перед опасным DELETE почти всегда стоит сделать SELECT с той же логикой.

Например, вместо того чтобы сразу выполнять:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

сначала проверьте:

SELECT
  o.id,
  o.user_id,
  o.amount,
  u.email,
  u.country
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

Если результат выглядит правильно, можно запускать DELETE.

Ещё безопаснее — делать это в транзакции:

BEGIN;

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

-- check the number of deleted rows
-- if everything looks fine:
COMMIT;

-- if something is wrong:
-- ROLLBACK;

Для опасных удалений это хорошая привычка.

Главная ошибка: забыли условие связи

Самая страшная ошибка в DELETE ... USING — забыть условие связи между таблицами.

Опасный запрос:

DELETE FROM orders o
USING users u
WHERE u.country = 'US';

Здесь нет условия:

o.user_id = u.id

Что произойдёт?

PostgreSQL построит декартово произведение orders и пользователей из США.

Если в users есть хотя бы один пользователь из США, каждая строка orders найдёт себе пару.

В результате может удалиться вся таблица orders.

Правильно:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Главное правило:

В DELETE ... USING всегда проверяйте условие связи между целевой таблицей и таблицами из USING.

Несколько таблиц в USING

В USING можно указать несколько таблиц.

Например, есть:

• orders;
• users;
• employees.

Нужно удалить заказы пользователей, которые связаны с менеджерами из отдела sales.

DELETE FROM orders o
USING users u, employees e
WHERE o.user_id = u.id
  AND u.manager_id = e.id
  AND e.dept = 'sales';

Здесь удаляем только из orders.

Таблицы users и employees используются для отбора.

Логика такая:

orders связываем с users
users связываем с employees
оставляем только employees.dept = 'sales'
удаляем подходящие orders

Можно представить это как JOIN нескольких таблиц, только результатом будет не выборка, а удаление строк из целевой таблицы.

Если справа несколько совпадений

Допустим, для одной строки orders нашлось несколько строк в таблицах из USING.

Это не значит, что строка удалится несколько раз.

Она просто будет удалена один раз.

Например:

DELETE FROM orders o
USING order_tags t
WHERE o.id = t.order_id
  AND t.tag IN ('test', 'spam');

Если у заказа есть оба тега:

test
spam

он может попасть в соединение дважды, но физически строка orders удалится один раз.

Дубликаты справа не делают несколько удалений одной и той же строки.

Но они могут влиять на план и производительность, поэтому в сложных случаях всё равно полезно проверять результат через SELECT.

DELETE ... USING с RETURNING

В PostgreSQL можно добавить RETURNING, чтобы увидеть, какие строки были удалены.

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US'
RETURNING o.id, o.user_id, o.amount;

RETURNING вернёт удалённые строки из orders.

Пример результата:

id  | user_id | amount
----+---------+--------
101 | 1       | 1000
103 | 3       | 700

Это удобно для:

• аудита;
• логирования;
• проверки;
• переноса удалённых строк в архив;
• понимания, что реально исчезло.

Можно вернуть и поля из таблицы USING:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US'
RETURNING
  o.id AS order_id,
  o.amount,
  u.email,
  u.country;

Так можно сразу увидеть не только удалённый заказ, но и причину удаления.

Архивирование через DELETE ... RETURNING

DELETE ... USING ... RETURNING можно использовать вместе с CTE.

Например, удалить заказы пользователей из США и записать их в архив.

WITH deleted_orders AS (
  DELETE FROM orders o
  USING users u
  WHERE o.user_id = u.id
    AND u.country = 'US'
  RETURNING
    o.id,
    o.user_id,
    o.amount,
    o.status,
    now() AS archived_at
)
INSERT INTO orders_archive (id, user_id, amount, status, archived_at)
SELECT
  id,
  user_id,
  amount,
  status,
  archived_at
FROM deleted_orders;

Что происходит:

1. DELETE удаляет строки из orders;
2. RETURNING возвращает удалённые строки;
3. внешний INSERT сохраняет их в архив.

Это один SQL-запрос.

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

DELETE ... USING против IN

Ту же задачу можно решить через IN.

DELETE FROM orders
WHERE user_id IN (
  SELECT id
  FROM users
  WHERE country = 'US'
);

Это корректный запрос.

Он означает:

удали заказы, где user_id входит в список пользователей из США

Вариант через USING:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Оба варианта могут дать одинаковый результат.

Но USING часто удобнее, когда:

• условий несколько;
• нужно связать несколько таблиц;
• нужно использовать поля второй таблицы в RETURNING;
• хочется писать запрос в стиле JOIN;
• подзапрос через IN становится громоздким.

DELETE ... USING против EXISTS

Есть ещё вариант через EXISTS.

DELETE FROM orders o
WHERE EXISTS (
  SELECT 1
  FROM users u
  WHERE u.id = o.user_id
    AND u.country = 'US'
);

Это тоже правильный и часто хороший вариант.

Он читается как:

удали заказ, если существует подходящий пользователь

Когда выбирать что?

DELETE ... USING -- удобно, когда хочется JOIN-стиль и доступ к таблицам в USING
EXISTS           -- удобно для проверки существования связанной строки
IN               -- удобно для простого списка одного столбца

В PostgreSQL планировщик часто может построить похожие планы для этих вариантов.

Поэтому выбор обычно зависит не только от скорости, но и от читаемости.

Опасность NOT IN с NULL

Для удаления «тех, кого нет в списке», иногда пишут NOT IN.

Например:

DELETE FROM orders
WHERE user_id NOT IN (
  SELECT user_id
  FROM whitelist
);

Это может быть опасно, если в подзапросе есть NULL.

Если whitelist.user_id содержит хотя бы один NULL, логика NOT IN может дать неожиданный результат и не удалить ничего.

Безопаснее использовать NOT EXISTS.

Например, удалить заказы пользователей, которых нет в whitelist:

DELETE FROM orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM whitelist w
  WHERE w.user_id = o.user_id
);

Или через DELETE ... USING с отдельной подготовленной таблицей, если логика сложнее.

Главное правило:

Для анти-джойна чаще используйте NOT EXISTS, а не NOT IN, если в данных возможны NULL.

Удаление дублей через DELETE ... USING

DELETE ... USING часто используют для удаления дублей.

Допустим, в таблице users случайно есть дубликаты по email.

Нужно оставить строку с минимальным id, а остальные удалить.

DELETE FROM users u
USING users keep
WHERE u.email = keep.email
  AND u.id > keep.id;

Что здесь происходит:

• u — строка, которую потенциально удаляем;
• keep — другая строка с тем же email;
• если у u.id больше, значит есть более ранняя строка с таким email;
• значит u можно удалить.

Пример:

id | email
---+----------------
1  | ada@mail.com
5  | ada@mail.com
9  | ada@mail.com

Для id = 5 найдётся keep.id = 1.

Для id = 9 тоже найдётся keep.id = 1.

Они удалятся.

id = 1 останется, потому что нет строки с таким же email и меньшим id.

Перед таким удалением обязательно проверьте SELECT:

SELECT
  u.*
FROM users u
JOIN users keep
  ON u.email = keep.email
 AND u.id > keep.id;

Удаление по staging-таблице

Частый ETL-сценарий: есть staging-таблица с ключами строк, которые нужно удалить.

Например:

staging_deleted_orders
----------------------
order_id
101
205
333

Удаляем заказы из основной таблицы:

DELETE FROM orders o
USING staging_deleted_orders s
WHERE o.id = s.order_id;

Это проще и понятнее, чем строить большой IN (...).

Если нужно увидеть, что удалили:

DELETE FROM orders o
USING staging_deleted_orders s
WHERE o.id = s.order_id
RETURNING o.id, o.user_id, o.amount;

Такой подход удобно использовать в загрузках данных, синхронизации и обработке удалений из внешних систем.

Удаление по blacklist

Допустим, есть таблица заблокированных пользователей:

blocked_users
-------------
user_id

Удалим их сессии:

DELETE FROM sessions s
USING blocked_users b
WHERE s.user_id = b.user_id;

Или удалим события этих пользователей:

DELETE FROM events e
USING blocked_users b
WHERE e.user_id = b.user_id;

Такие запросы хорошо читаются: целевая таблица слева, список условий справа.

Удаление старых строк с дополнительной таблицей

Можно комбинировать условия по целевой таблице и таблице из USING.

Например, удалить старые pending-заказы пользователей из неактивных аккаунтов:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.active = false
  AND o.status = 'pending'
  AND o.created_at < now() - interval '90 days';

Здесь условия делятся на две группы.

Связь:

o.user_id = u.id

Фильтры:

u.active = false
o.status = 'pending'
o.created_at < now() - interval '90 days'

Такой запрос читается как обычный join-фильтр.

Индексы для DELETE ... USING

DELETE ... USING — это не магия. PostgreSQL всё равно должен найти строки.

Для примера:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Полезны индексы:

CREATE INDEX orders_user_id_idx
ON orders (user_id);

и, в зависимости от объёма и селективности:

CREATE INDEX users_country_id_idx
ON users (country, id);

или хотя бы primary key на users.id, который обычно уже есть.

Если удаляете по staging-таблице:

DELETE FROM orders o
USING staging_deleted_orders s
WHERE o.id = s.order_id;

полезны:

-- orders.id is usually already PRIMARY KEY
CREATE INDEX staging_deleted_orders_order_id_idx
ON staging_deleted_orders (order_id);

Перед большими удалениями проверяйте план:

EXPLAIN
DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

А если можно безопасно прогнать на тесте, используйте EXPLAIN ANALYZE.

Большие удаления лучше делать батчами

Если нужно удалить миллионы строк, один большой DELETE может быть проблемой.

Он может:

• долго держать блокировки;
• создать много dead tuples;
• раздуть таблицу и индексы;
• нагрузить autovacuum;
• долго откатываться при ошибке.

Для больших чисток часто используют батчи.

Например, через CTE выбирают ограниченный набор id, а потом удаляют его.

WITH to_delete AS (
  SELECT o.id
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE u.country = 'US'
  LIMIT 10000
)
DELETE FROM orders o
USING to_delete d
WHERE o.id = d.id;

Такой запрос можно запускать повторно, пока строки не закончатся.

Если нужен стабильный порядок батчей, добавьте ORDER BY в CTE:

WITH to_delete AS (
  SELECT o.id
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE u.country = 'US'
  ORDER BY o.id
  LIMIT 10000
)
DELETE FROM orders o
USING to_delete d
WHERE o.id = d.id;

DELETE и внешние ключи

Если удаляемые строки связаны внешними ключами, PostgreSQL может не дать удалить их.

Например, если есть order_items, которые ссылаются на orders:

order_items.order_id REFERENCES orders(id)

то такой запрос:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

может упасть, если у заказов есть связанные order_items.

Решения зависят от модели данных:

• сначала удалить дочерние строки;
• использовать ON DELETE CASCADE;
• запретить удаление и делать soft delete;
• архивировать данные;
• пересмотреть жизненный цикл сущности.

DELETE ... USING не обходит внешние ключи. Он просто удобнее формулирует условие удаления.

Soft delete вместо физического удаления

Иногда удалять строки физически не нужно.

Например, вместо:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

можно сделать:

UPDATE orders o
SET deleted_at = now()
FROM users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Это уже не DELETE ... USING, а UPDATE ... FROM, но идея похожа: целевая таблица обновляется по условиям из другой таблицы.

Soft delete полезен, когда:

• нужен аудит;
• данные нельзя терять физически;
• есть восстановление;
• есть юридические требования;
• удаление влияет на историю отчётов.

MySQL: DELETE с JOIN

В MySQL синтаксис другой.

Там обычно пишут многотабличный DELETE с JOIN.

Например, PostgreSQL:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

MySQL:

DELETE o
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

Ключевая часть:

DELETE o

Она говорит, из какой таблицы реально удалять.

В MySQL можно удалить сразу из нескольких таблиц:

DELETE o, u
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

Но это намного опаснее: такой запрос удалит строки и из orders, и из users.

В PostgreSQL DELETE ... USING удаляет только из целевой таблицы после DELETE FROM.

ClickHouse

В ClickHouse подход к удалению другой.

Там нет такого же DELETE ... USING в стиле PostgreSQL.

Удаления обычно выполняются через mutations, например:

ALTER TABLE orders
DELETE WHERE user_id IN (
  SELECT id
  FROM users
  WHERE country = 'US'
);

В новых версиях также есть более привычный синтаксис DELETE FROM, но семантика и производительность всё равно отличаются от PostgreSQL.

ClickHouse — колоночная аналитическая СУБД. Удаления там часто асинхронные и тяжёлые, особенно на больших объёмах.

Для массовых чисток в ClickHouse обычно думают не как в OLTP-базе, а через:

• партиции;
• TTL;
• пересоздание таблиц;
• фильтрацию при чтении;
• замену данных;
• движок таблицы и стратегию хранения.

При переносе PostgreSQL-запроса с DELETE ... USING в ClickHouse синтаксис нужно переписывать и проверять на конкретной версии.

Практические шаблоны

Удалить заказы пользователей из страны

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Проверить перед удалением

SELECT
  o.id,
  o.user_id,
  o.amount,
  u.email,
  u.country
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

Удалить и вернуть удалённые строки

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US'
RETURNING o.id, o.user_id, o.amount;

Удалить по staging-таблице

DELETE FROM orders o
USING staging_deleted_orders s
WHERE o.id = s.order_id;

Удалить события заблокированных пользователей

DELETE FROM events e
USING blocked_users b
WHERE e.user_id = b.user_id;

Удалить старые pending-заказы неактивных пользователей

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.active = false
  AND o.status = 'pending'
  AND o.created_at < now() - interval '90 days';

Удалить дубли, оставив минимальный id

DELETE FROM users u
USING users keep
WHERE u.email = keep.email
  AND u.id > keep.id;

Удалять батчами

WITH to_delete AS (
  SELECT o.id
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE u.country = 'US'
  ORDER BY o.id
  LIMIT 10000
)
DELETE FROM orders o
USING to_delete d
WHERE o.id = d.id;

MySQL-аналог

DELETE o
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

Частые ошибки

Забыли join-условие

Опасно:

DELETE FROM orders o
USING users u
WHERE u.country = 'US';

Правильно:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Думают, что USING удаляет из второй таблицы

Запрос:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id;

удаляет только из orders.

users не удаляется.

Используют NOT IN с NULL

Опасно:

DELETE FROM orders
WHERE user_id NOT IN (
  SELECT user_id
  FROM whitelist
);

Лучше:

DELETE FROM orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM whitelist w
  WHERE w.user_id = o.user_id
);

Не проверяют SELECT перед DELETE

Перед сложным удалением лучше сначала сделать SELECT с той же логикой.

SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

Делают огромное удаление одной транзакцией

Большой DELETE может быть тяжёлым.

Для миллионов строк лучше подумать о батчах, партициях, архивировании или soft delete.

Что важно запомнить

DELETE ... USING позволяет удалять строки из одной таблицы, фильтруя их по другой таблице.

Пример:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Главные правила:

• удаляется только таблица после DELETE FROM;
• таблицы в USING нужны для фильтрации;
• связь между таблицами пишется в WHERE;
• USING похож на JOIN, но без отдельного ON;
• можно использовать несколько таблиц в USING;
• если справа несколько совпадений, целевая строка удаляется один раз;
• забытое условие связи может удалить слишком много данных;
• перед удалением полезно проверить такой же SELECT;
• RETURNING помогает увидеть удалённые строки;
• для анти-условий безопаснее NOT EXISTS, чем NOT IN с возможными NULL;
• для больших удалений лучше использовать батчи;
• в MySQL аналог пишется через DELETE ... JOIN.

Короткий вывод

DELETE ... USING — удобный PostgreSQL-синтаксис для удаления по связанной таблице.

Например:

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

читается так:

удали заказы,
для которых найден пользователь
из страны US

Главная мысль:

DELETE FROM говорит, откуда удаляем. USING говорит, какие таблицы используем для отбора. WHERE связывает таблицы и задаёт фильтры.

Используйте DELETE ... USING, когда условие удаления естественно описывается через другую таблицу. Но перед запуском всегда проверяйте join-условие и результат через SELECT: ошибка в DELETE редко прощает невнимательность.