PostgreSQL. Использование USING и RETURNING в команде DELETE.

В заметке рассказывается использование предложений USING и RETURNING в команде DELETE.

Воспользуемся известной схемой SCOTT. Допустим, мы хотим удалить из таблицы emp всех работников из департамента с названием SALES.

Таблица департаментов dept

По стандарту эту задачу можно решить так:

delete
from scott.emp e
where e.deptno in (select deptno from scott.dept d where d.dname = 'SALES');

Но PostgreSQL при удалении позволяет ссылаться на столбцы других таблиц в условии WHERE, когда эти таблицы перечисляются в предложении USING. Таким образом решение указанной задачи с использованием предложения USING выглядит так:

delete
from scott.emp e
using scott.dept d
where e.deptno = d.deptno and d.dname = 'SALES';

По сути, это дилит с джойном. В MS SQL Server можно было бы в лоб написать join (код ниже), а PostgreSQL пошёл своим путём.

-- вариант удаления в ms sql server через join 
-- (но это не точно, пишу по памяти)
delete e
from scott.emp e
join scott.dept d
  on e.deptno = d.deptno and d.dname = 'SALES';

В документации пишется, что "В ряде случаев запрос в стиле соединения легче написать и он может работать быстрее, чем в стиле вложенного запроса". Так это или нет можно убедиться только на практике.

Кстати, чтобы посмотреть реальный план команды DELETE без фактического её выполнения, можно начать транзакцию, выполнить explain analyze запроса, и потом откатить выполнение командой rollback (рисунок ниже).

Просмотр реального плана выполнения команды с последующим откатом.

Если бы вдруг перед удалением захотелось посмотреть, какие записи из таблицы удаляются, то помогла бы кляуза RETURNING.

Вывод удаляемых записей

Также RETURNING может использоваться в командах INSERT, UPDATE и MERGE для получения данных из модифицируемых строк. Подробно об этом можно почитать в документации.