Yes, I agree that for most SELECT statements the ON clause is seen as a filtering condition, particularly for inner joins when the ON condition is equivalent to the WHERE condition.

Then your comment about "The behavior in the MERGE statement is different" got me thinking about why it's different. It occured to me that the function of the ON clause in MERGE seems very similar to the ON clause in a FULL OUTER JOIN.

If we equate the left table in a FULL OUTER JOIN to the MERGE target, then this table shows the comparison between the behaviour of the two statements assuming they use the same ON clause.

Left rows Right rows FULL OUTER JOIN MERGE

>= 1 1 combine both execute WHEN MATCHED

1 0 right fields are null execute WHEN NOT MATCHED BY SOURCE

0 1 left fields are null execute WHEN NOT MATCHED BY TARGET

1 > 1 combine both error

So maybe it's true to say that using a funky ON clause in a MERGE requires the same care and understanding as using it in a FULL OUTER JOIN??