Оператор MERGE¶

Оператор MERGE — это оператор языка SQL, относящийся к категории DML (Data Manipulation Language), который позволяет выполнять операции вставки (INSERT), обновления (UPDATE) и удаления (DELETE) в одной инструкции на основе сравнения исходных данных с данными целевой таблицы. Оператор также известен как «upsert» (от англ. update + insert) и применяется для синхронизации содержимого двух таблиц или для условного обновления записей с возможностью их добавления при отсутствии.

¶История и стандартизация

Концепция операции, объединяющей вставку и обновление, существовала в различных СУБД задолго до формальной стандартизации. В 1999 году оператор MERGE был включён в стандарт SQL:1999 (ISO/IEC 9075:1999) как часть расширения языка для работы с данными. Однако ранние реализации, такие как команда MERGE в Oracle Database (появилась в версии 9i, выпущенной в 2001 году), отличались от более позднего стандарта.

В последующих версиях стандарта — SQL:2003, SQL:2008, SQL:2011 и SQL:2016 — синтаксис и семантика оператора были уточнены, в частности, добавлена возможность выполнения операций DELETE в рамках MERGE и уточнены правила обработки дублирующихся строк. В настоящее время оператор MERGE поддерживается большинством реляционных СУБД, включая Oracle Database, Microsoft SQL Server (начиная с версии 2008), PostgreSQL (с версии 9.5), IBM Db2, SAP HANA, а также в ограниченном виде — в MySQL (через конструкцию INSERT ... ON DUPLICATE KEY UPDATE) и SQLite (через INSERT OR REPLACE или UPSERT).

¶Синтаксис

Общий синтаксис оператора MERGE в соответствии со стандартом SQL:2008 выглядит следующим образом:

``sql MERGE INTO target_table AS target USING source_table AS source ON condition WHEN MATCHED THEN UPDATE SET column1 = value1, column2 = value2, ... WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (value1, value2, ...) WHEN NOT MATCHED BY SOURCE THEN DELETE; ``

¶Ключевые элементы синтаксиса

target_table — целевая таблица, в которую выполняются изменения.
source_table — источник данных (может быть таблица, представление, подзапрос или табличная функция).
ON condition — условие соединения, определяющее соответствие строк источника и цели.
WHEN MATCHED — блок, выполняемый для строк, которые присутствуют как в источнике, так и в цели.
WHEN NOT MATCHED — блок для строк, присутствующих только в источнике (вставка).
WHEN NOT MATCHED BY SOURCE — блок для строк, присутствующих только в цели (удаление или обновление).

В некоторых реализациях (например, в Oracle) поддерживается также клаузула WHEN NOT MATCHED THEN INSERT без обязательного указания BY SOURCE, а также возможность указания LOG ERRORS для обработки ошибок.

¶Примеры использования

¶Пример 1: базовая синхронизация таблиц

Пусть имеется таблица employees (целевая) и таблица new_employees (источник). Необходимо обновить данные существующих сотрудников и добавить новых.

``sql MERGE INTO employees AS e USING new_employees AS n ON e.employee_id = n.employee_id WHEN MATCHED THEN UPDATE SET e.salary = n.salary, e.department = n.department WHEN NOT MATCHED THEN INSERT (employee_id, name, salary, department) VALUES (n.employee_id, n.name, n.salary, n.department); ``

¶Пример 2: удаление отсутствующих записей

Если требуется также удалить из целевой таблицы записи, которых нет в источнике, добавляется клаузула WHEN NOT MATCHED BY SOURCE:

¶Особенности и ограничения

¶Производительность

Оператор MERGE является атомарным — все изменения выполняются в рамках одной транзакции, что снижает накладные расходы на передачу данных между клиентом и сервером по сравнению с последовательным выполнением отдельных операций INSERT, UPDATE и DELETE. Однако при работе с большими объёмами данных может потребоваться настройка плана выполнения, так как некоторые СУБД (например, SQL Server) могут генерировать неоптимальные планы для MERGE.

¶Конфликты и дублирование

При использовании MERGE важно, чтобы условие ON однозначно идентифицировало строки. Если в источнике или цели есть дублирующиеся строки по условию соединения, возникает конфликт, который может привести к ошибке. Стандарт SQL:2008 требует, чтобы каждая строка цели соединялась не более чем с одной строкой источника; в противном случае СУБД генерирует исключение.

¶Обработка ошибок

Некоторые СУБД (например, Oracle) предоставляют возможность логирования ошибок с помощью клаузулы LOG ERRORS, что позволяет продолжить выполнение MERGE даже при возникновении ошибок в отдельных строках. В других СУБД (например, PostgreSQL) для обработки ошибок может потребоваться использование блоков EXCEPTION в хранимых процедурах.

¶Различия в реализациях

Oracle Database — поддерживает MERGE с 9i. Допускает использование WHEN NOT MATCHED THEN INSERT без BY SOURCE, а также UPDATE в блоке WHEN MATCHED без указания всех столбцов.
Microsoft SQL Server — поддерживает MERGE с 2008. Требует указания BY TARGET и BY SOURCE в клаузулах WHEN NOT MATCHED. Может генерировать неоптимальные планы при наличии триггеров.
PostgreSQL — поддерживает MERGE с версии 15. До этого использовалась конструкция INSERT ... ON CONFLICT DO UPDATE (UPSERT), которая является более ограниченной.
MySQL — не поддерживает MERGE в стандартном виде. Вместо этого используется INSERT ... ON DUPLICATE KEY UPDATE, который выполняет только вставку и обновление, но не удаление.
SQLite — поддерживает INSERT OR REPLACE и UPSERT (с версии 3.24.0), но не полный MERGE.

¶Альтернативы

В СУБД, не поддерживающих MERGE, или в случаях, когда его использование нежелательно (например, из-за проблем с производительностью), применяются следующие подходы:

Последовательное выполнение UPDATE и INSERT — сначала выполняется обновление существующих записей, затем вставка отсутствующих. Этот метод может быть менее эффективным из-за двух отдельных транзакций.
Использование временных таблиц — данные загружаются во временную таблицу, после чего выполняются операции слияния с помощью INSERT и UPDATE.
Конструкция INSERT ... ON DUPLICATE KEY UPDATE (MySQL) — позволяет выполнить вставку или обновление, но не поддерживает удаление.
Конструкция INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) — аналог UPSERT, поддерживаемый с версии 9.5.

¶Применение

Оператор MERGE широко используется в следующих сценариях:

Синхронизация данных — обновление целевой таблицы на основе изменений в источнике (например, загрузка данных из OLTP-системы в хранилище данных).
Загрузка измерений (SCD) — в системах бизнес-аналитики (BI) для обработки медленно меняющихся измерений (Slowly Changing Dimensions, SCD) типа 1 и 2.
Обработка потоков данных — в ETL-процессах (Extract, Transform, Load) для инкрементальной загрузки.
Управление справочниками — обновление и дополнение справочных таблиц без ручного вмешательства.

¶Критика

Несмотря на широкое распространение, оператор MERGE подвергается критике по нескольким причинам:

Сложность планов выполнения — некоторые СУБД (особенно SQL Server) могут генерировать неоптимальные планы, что приводит к снижению производительности.
Проблемы с параллелизмом — при одновременном выполнении нескольких операций MERGE на одной таблице могут возникать взаимоблокировки (deadlocks).
Неоднозначность семантики — в ранних реализациях (например, Oracle 9i) поведение MERGE отличалось от стандарта, что приводило к ошибкам при переносе кода.
Ограниченная поддержка — в некоторых СУБД (MySQL, SQLite) отсутствует полноценная реализация, что вынуждает разработчиков использовать обходные пути.

¶Источники

ISO/IEC 9075:1999, 9075:2003, 9075:2008 — стандарты языка SQL.
Документация Oracle Database по оператору MERGE (версии 9i–23c).
Документация Microsoft SQL Server по оператору MERGE (версии 2008–2022).
Документация PostgreSQL по оператору MERGE (версии 15–16).
Статья «MERGE (SQL)» в английской Википедии.
Книга «SQL: The Complete Reference» (James R. Groff, Paul N. Weinberg, 3-е издание, 2009).

BFOmetr — база данных и аналитика по компаниям России.

На главную BFOmetr →