UPSERT synonym for MERGE?

Question

I created a tag upsert (7x) today and wrote a somewhat useful (I hope) tag wiki.

Later I found that there is already a tag merge (15x, no wiki). Would you consider the two synonymous? I can't mark them as such, my score for merge is too low.

Answer 1

No they are not synonymous.

An UPSERT can be implemented without using the MERGE keyword and conversely UPSERT is just one possible use case for MERGE (when it has both a WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED [ BY TARGET ] THEN INSERT ...)

At least in SQL Server it is not required that MERGE have both (or indeed either) of these clauses.

The following use of MERGE neither updates nor inserts.

CREATE TABLE T(A INT)

INSERT INTO T VALUES (1),(2),(3);

MERGE INTO T
USING (VALUES (1),
              (2)) S(A)
ON T.A = S.A
WHEN NOT MATCHED BY SOURCE THEN
  DELETE; 

Other (possibly SQL Server centric) use cases for Merge that have nothing to do with Upserts is inserting DEFAULT VALUES for multiple rows or Using merge..output to get mapping between source.id and target.id