SQL MERGE

The Documentation says a bit ambiguously:

First, the MERGE command performs a join from data_source to the target table producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just once

What kind of join?

It’s source LEFT JOIN target.

Consider these two tables:

hjp=> create table target(id int, m text);
CREATE TABLE
Time: 18.459 ms
hjp=> insert into target(id) values(1), (2), (3), (4);
INSERT 0 4
Time: 5.186 ms
hjp=> create table source(id int);
CREATE TABLE
Time: 4.609 ms
hjp=> insert into source(id) values (3), (4), (5), (6);
INSERT 0 4
Time: 5.708 ms

Then:

hjp=> select * from source left join target on source.id = target.id;
╔════╤═════╤═════╗
║ id │ id  │  m  ║
╟────┼─────┼─────╢
║  3 │   3 │ (∅) ║
║  4 │   4 │ (∅) ║
║  5 │ (∅) │ (∅) ║
║  6 │ (∅) │ (∅) ║
╚════╧═════╧═════╝
(4 rows)

So Ids 3 and 4 will match, 5 and 6 will not match and 1 and 2 will not be considered at all:

hjp=>
merge into target
using source on target.id = source.id
when     matched and source.id is not null then update set m = 'm'
when     matched and source.id is     null then update set m = 'nmo'
when not matched                           then insert values (id, 'nmn')
;
MERGE 4
Time: 0.443 ms
hjp=*> select * from target;
╔════╤═════╗
║ id │  m  ║
╟────┼─────╢
║  1 │ (∅) ║
║  2 │ (∅) ║
║  3 │ m   ║
║  4 │ m   ║
║  5 │ nmn ║
║  6 │ nmn ║
╚════╧═════╝
(6 rows)

The rows 1 and 2 are unaffected, the condition matched and source.id is null can never happen. And not matched only allows an insert, no delete, so it’s clear that we are considering only the source, not the target here.