upsert is built on top of insert while merge allows you specify multiple conditions and includes the ability to perform a delete.

i.e if you have a blog post with a list of tags, you could say:

- if submitted tag is not found > insert it

- if submitted tag is found > do nothing

- if tag exists that wasn't submitted > delete it

It can be quite powerful but it can get difficult to read also. I just hate using it in SQL Server because every time someone adds it, a year later its the code causing deadlocks.