ON clause im MERGE

Zu meinen Lieblingselementen in SQL gehört das MERGE-Statement, das dazu dient, bestehende Sätze einer Zieltabelle zu aktualisieren und neue Sätze zu ergänzen - abhängig davon, ob eine gegebene Join-Bedingung zutrifft oder nicht. Ich verwende MERGE dieser Tage oft auch dann, wenn eigentlich ein INSERT oder ein UPDATE genügen würden. Die Syntax von MERGE erfordert zwar eine gewisse Gewöhnung, ist dann aber ausgesprochen einleuchtend und nachvollziehbar. Gestern ist mir aber aufgefallen, dass ich einen zentralen Aspekt des MERGE bisher nicht richtig eingeordnet hatte: nämlich die Rolle der ON Klausel, über die der Join von Quelle und Ziel definiert wird.

Die Dokumentation definiert das ON folgendermaßen:

Use the ON clause to specify the condition upon which the MERGE operation either updates or inserts. For each row in the target table for which the search condition is true, Oracle Database updates the row with corresponding data from the source table. If the condition is not true for any rows, then the database inserts into the target table based on the corresponding source table row.

Entscheidend dabei ist, dass das ON ausschließlich den Join betrifft und nicht zur Definition zusätzlicher Filterkriterien missbraucht werden kann. Dazu ein Beispiel: Ich lege zwei simple Testtabellen an, eine Quelltabelle mit 10 Sätzen und dem Kennungstext 'source' und eine Zieltabelle mit dem Text 'dest':

drop table test_merge_dest;
create table test_merge_dest
as
select rownum id
     , cast ('dest' as varchar2(6)) source_tab
  from dual
connect by level <= 10;

        ID SOUR
---------- ----
         1 dest
         2 dest
         3 dest
         4 dest
         5 dest
         6 dest
         7 dest
         8 dest
         9 dest
        10 dest

drop table test_merge_source;
create table test_merge_source
as
select rownum id
     , 'source' source_tab
  from dual
connect by level <= 10;

        ID SOURCE
---------- ------
         1 source
         2 source
         3 source
         4 source
         5 source
         6 source
         7 source
         8 source
         9 source
        10 source

Ein MERGE der Daten über die Id-Spalte führt dazu, dass alle Sätze der Zieltabelle durch ein Update überschrieben werden:

merge into test_merge_dest dst
using (
select *
  from test_merge_source) src
on (dst.id = src.id)
when matched then update set
  dst.source_tab = src.source_tab
when not matched then insert (
    dst.id
  , dst.source_tab
)
values (
    src.id
  , src.source_tab
);

10 Zeilen integriert.

select * from test_merge_dest;

        ID SOURCE
---------- ------
         1 source
         2 source
         3 source
         4 source
         5 source
         6 source
         7 source
         8 source
         9 source
        10 source

rollback;

So weit ganz harmlos. Nicht harmlos ist es aber, wenn jemand in das ON eine zusätzliche Filterbedingung für die Quelle einbauen will (was syntaktisch auch nicht naheliegend ist und mir bisher nie in den Sinn gekommen war):

merge into test_merge_dest dst
using (
select *
  from test_merge_source) src
on (dst.id = src.id and src.id <= 5)
when matched then update set
  dst.source_tab = src.source_tab
when not matched then insert (
    dst.id
  , dst.source_tab
)
values (
    src.id
  , src.source_tab
);

10 Zeilen integriert.

select * from test_merge_dest;

        ID SOURCE
---------- ------
         1 source
         2 source
         3 source
         4 source
         5 source
         6 dest
         7 dest
         8 dest
         9 dest
        10 dest
         8 source
        10 source
         6 source
         7 source
         9 source

15 Zeilen ausgewählt.

In diesem Fall erfolgt nicht etwa eine zusätzliche Filterung der Quelldaten, sondern eine Änderung der Join-Bedingung: integriert werden immer noch alle 10 Sätze der Quelle, aber 5 werden als Update verarbeitet und 5 als Insert.

Noch deutlicher wird das zugrunde liegende Problem, wenn man sich klar macht, dass das MERGE einen OUTER JOIN darstellt und dabei ist es natürlich relevant, ob man eine Bedingung als Join- oder als Filterkriterium definiert (was in der Syntax mit JOIN-Schlüsselwort deutlicher wird als in der traditionellen Schreibweise mit der Auflistung aller Bedingungen im WHERE).