Currently BOL warns that certain ON clauses in the MERGE statement, while legal, can produce what it calls "incorrect" results, i.e. results that are clearly unexpected based on the syntax. The compiler should issue a warning in such cases, at least whenever it can detect the problem. (In some of the cases it is simply a matter of moving an extra ON clause restriction, such as comparison to a constant, to the WHEN section.) See BOL here:http://msdn.microsoft.com/en-us/library/bb510625.aspxDo not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.AND here:http://msdn.microsoft.com/en-us/library/cc879317.aspxDo not include comparisons to other values such as a constant....This method [i.e. using CTEs to filter source or target rows in a MERGE] is similar to specifying additional search criteria in the ON clause and may produce incorrect results. We recommend that you avoid using this method or test thoroughly before implementing it. AND here:http://msdn.microsoft.com/en-us/library/bb522522.aspxBecause the additional search condition is not required to determine source and target matching, the insert and delete actions are applied to all input rows. In effect, the filtering condition EmployeeName LIKE 'S%' is ignored. When the statement is run, the output of the inserted and deleted tables shows that two rows are incorrectly modified.