如何理解T-SQL中Merge语句

 

http://www.cnblogs.com/wuguanglei/p/4222905.html

 

写在前面的话：之前看过Merge语句，感觉没什么用，完全可以用其他的方式来替代，最近又看了看Merge语句，确实挺好用，可以少写很多代码，看起来也很紧凑，当然也有别的优点。

====正文开始=====

　　SQL Server 2008 引入了Merge关键字，主要是在一条语句里面可以执行insert、update、delete操作，以实现用一个源对象的数据对目标对象数据进行操作。注意这里的”源对象“和”目标对象“我用黑色标注了，源对象和目标对象实际上不仅仅可以是表Table，还可以是临时表、视图、表变量、CTE，同时”目标对象“还可以是Select语句，说这么多其实想表达Merge语句可以很灵活的使用，但是我们理解的话，可以把”源对象“和”目标对象“想象成Table就行了，毕竟临时表、视图、表变量、CTE也都可以想象成Table。

（注意：如果目标对象是视图的话，那么对目标对象的操作，如update，实际上是对生成视图的表进行操作的）。

　　好了，看完上面的文字，你可能已经看不下去了，之所以写上面的话，是为了显得严谨一些，接下来就用例子来讲解吧,这个例子不涉及业务逻辑，可以专注于理解Merge的用法，至于实际中什么时候用，只能自己悟了，好了，开始举例子。

　　例如有一个Student_Target表，如下表一，另外一个Student_Source表，如下表二：

　　执行如下SQL语句：

1 2 3 4 5 6 7 8 9 10 11

MERGE INTO Student_Target AS st　　 --这里是目标表，它将要被源表Merge USING Student_Source AS ss　　　　   --这里是源表 ON st.Sno = ss.Sno　　　　　　　　　　--这里是匹配条件     WHEN MATCHED　　　　　　　　　　　--When和Then是配套的，当st.sno=ss.sno时，用ss.sname更新st.sname，我们看到这里update后面没有写明更新的表，这里更新的是目标表         THEN UPDATE SET st.Sname = ss.Sname      WHEN NOT MATCHED BY TARGET　　--目标表中不存在，而源表中存在数据，那么就执行insert操作，这里by target可以省略，但是建议加上         THEN INSERT VALUES    ( ss.Sno,ss.Sname)     WHEN NOT MATCHED BY SOURCE   --当目标表中存在，而源表中不存在数据，那么就执行delete操作，这里使用了by source         THEN DELETE ;

　　上面SQL语句的意思可以看后面的注释，这里再做一简要说明：对于表一，sno=1的一行和表二sno=1的匹配，所以表一中该行被更新；表一中sno=2,3在表二中不存在，因此delete，表二中sno=4但是在表一中不存在，因此insert，最后结果如下：

　　我们看到上面的结果和表二的内容是一样的，其实你再分析一下上面的SQL语句，逻辑就是把表二的内容弄进表一，表一中和表二中不一致的数据删除，似乎我们上面的Merge语句显得很多余。这里想再说明几点：

（1）我们When matched、When not matched by target、when not matched by source都写上了，其实是可选的，我们可以根据自己的需求只使用其中的部分。

（2）前面是Merge into Student_Target as st，其实可以增加top(n)来对特定数量的行进行操作。执行如下SQL： 

1 2 3 4 5 6 7 8 9 10 11

MERGE TOP(2) INTO Student_Target AS st　　 --这里是目标表，它将要被源表Merge USING Student_Source AS ss　　　　   --这里是源表 ON st.Sno = ss.Sno　　　　　　　　　　--这里是匹配条件     WHEN MATCHED　　　　　　　　　　　--When和Then是配套的，当st.sno=ss.sno时，用ss.sname更新st.sname，我们看到这里update后面没有写明更新的表，这里更新的是目标表         THEN UPDATE SET st.Sname = ss.Sname      WHEN NOT MATCHED BY TARGET　　--目标表中不存在，而源表中存在数据，那么就执行insert操作，这里by target可以省略，但是建议加上         THEN INSERT VALUES    ( ss.Sno,ss.Sname)     WHEN NOT MATCHED BY SOURCE   --当目标表中存在，而源表中不存在数据，那么就执行delete操作，这里使用了by source         THEN DELETE ;

 　　最后结果如下：

　　所以，增加了top（2），那么目标表可以被操作的行只能是2条，上面的update操作一条，insert操作一条，达到2条，因此后面的delete就不影响了。因此，对于top(n)应该能够正确的理解。 

（3）前面when matched 其实还可以配合其他条件一起操作，例如when matched 可以修改为when matched and ss.sno=1或者when matched and st.sno=1；对于when not matched，只能够使用源列，也就是说，增加and ss.sno=1可以，但是增加and st.sno=1就会报错了。

（4）Merge最后一定要以分号结尾，表示这个Merge句子完整了。

　　前面的操作我们看到默认的都是对Target表的操作，有时候我们想对Target表操作后，还能够针对特定的条件，对Source表进行操作，这是就可以配合Output子句一起，来完成我们想要的操作。这里Output字句不单单是针对merge语句的，对于insert、update、delete等操作也可以用的，所以具体的可以再去单独研究研究output子句。

　　至此，本文也该告一段落，如何使用Merge语句应该也没有问题。可是心中仍然有一个结，正如上篇文章所写的：如何理解group by和聚合函数中对group by和聚合函数的认识一样，虽然用起来不成问题，但是总是希望能够找到一个天马行空的想法，能够换个角度去认识。比如为啥基本上都是when matched后面跟update，when not matched by target后面跟insert,when not matched by source 后面跟delete呢？为啥when not matched by target后面不能跟delete？为啥when not matched by source后面不能跟insert呢？当然可能还有其他疑问，目前可以跟join结合起来应该能够很好的解释清楚，可是现在还无法用很好的文字逻辑去表达清楚，回头想好怎么写了再写吧，请原谅我又”胡思乱想“了。

 

写在前面的话：上一篇写了如何理解T-SQL中Merge语句，基本把Merge语句要讲的给讲了，在文章的后面，抛出了几个结，当时没有想明白怎么去用文字表达，这一篇就来解答一下这几个结，又是一篇“天马行空”的文字，大家凑合看吧。

===正文开始===

　　先看下面表一（Student_Target）和表二（Student_Source）。

一、When Matched部分　　

　　执行下面SQL语句：

1 2 3 4 5 6

MERGE INTO Student_Target AS st USING Student_Source AS ss ON st.Sno = ss.Sno     WHEN MATCHED         THEN UPDATE SET st.Sname = ss.Sname ;

　　执行完上面SQL语句后，现在的Student_Target表的内容应该也很容易得到，如下图：

　　Student_Target表中Sno=1,2的行的Sname值被Student_Source中相应的值update，不过这个结果是怎么来的呢？今天用另外一种思路来理解一下怎么得到的，我们增加一个中间过程，表述如下：

　　（1）上面的SQL语句：MERGE INTO......WHEN MATCHED，可以类比成一个inner join语句：select * from Student_Target as st inner join Student_Source as ss on st.Sno=ss.Sno，内部联接后，结果如下图：

 

　　其中红色框内是Student_Target部分，蓝色为Student_Source部分，这个图表示的是什么呢？我们可以认为后面执行的操作仅仅影响图中红色的Student_Target部分，因此，执行Then update set st.sname=ss.sname，那么原始表一Student_Target中只有上图红色框中的部分受影响，即红框中Sname依次被蓝框内Sname给update了，其他行(3,'cc')不受影响，因此Student_Target最后结果为：

　　（2）照此思路，如果Then update set st.sname=ss.sname改成Then delete，自然只是红色框中部分被删除，最后Student_Target结果如下图，仅仅留下了一行。

　　（3）照此思路，如果Then update set st.sname=ss.sname改成Then insert values(ss.sno,ss.sname)，会出现什么情况呢？再看前面的红色和蓝色部分，红色部分内容不为NULL，因此无法用右边的蓝色部分给insert进去，所以应该报错，执行一下，果然报下面的错误：

　　　

　　现在应该明白When Matched为什么不允许有insert语句了吧，那么什么时候允许insert语句呢，接着往下看。

二、When NOT Matched BY Target部分　　

　　前面When Matched我们通过inner join的思路进行了理解，这次When NOT Matched BY Target我们用right outer join的思路去想想，同样原始表格还是表一和表二，再次贴图如下：

　

执行下面SQL语句：

1 2 3 4 5 6

MERGE INTO Student_Target AS st USING Student_Source AS ss ON st.Sno = ss.Sno     WHEN NOT MATCHED BY TARGET         THEN INSERT VALUES(ss.Sno,ss.Sname) ;

　　先不用着急看上面运行结果，我们先用right outer join的思路去梳理一下：

　　（1）上面的SQL语句：MERGE INTO......WHEN NOT MATCHED BY TARGET，可以类比成一个right outer join语句：select * from Student_Target as st right outer join Student_Source as ss on st.Sno=ss.Sno，右外部联接后（你应该对right outer join 概念非常清晰吧），结果如下图：

 

　　其中红色框内是Student_Target部分，蓝色为Student_Source部分，这个图表示的是什么呢？我们可以认为后面执行的操作仅仅影响图中红色的Student_Target部分，因此，执行Then insert values(ss.Sno,ss.Sname)，那么原始表一Student_Target中只有上图红色框中的部分受影响，即红框中Sno和Sname为null的依次被蓝框内Sno和Sname给insert了，这里之所以可以insert，就是因为第三行和第四行里面有null的值，因此最后Student_Target的运行结果为：

　　（2）照此思路，如果Then insert values(ss.Sno,ss.Sname)改成Then delete，自然只是红色框中部分被删除，但是红色部分的第三行和第四行是全为NULL的值，全null了自然无法删除啊，因此应该报错，运行一下，果然报错如下：

　　（3）照此思路，如果Then insert values(ss.Sno,ss.Sname)改成Then update set st.Sname=ss.Sname，同样因为全null的行会报错，因为全null无法更新啊，报错信息如下：

　　　

　　现在应该明白When NOT Matched BY Target为什么不允许有update和delete语句了吧。

三、When NOT Matched BY SOURCE部分　　

　　前面When Matched我们通过inner join的思路进行了理解，When NOT Matched BY Target用right outer join的思路去理解了，这次的When not matched by source自然应该用left outer join 来理解了，具体理解过程仿照第二部分，就不详细写了，直接上结果吧：

　　同样原始表格还是表一和表二，贴图如下：

　　（1）执行如下SQL语句：select * from Student_Target as st left outer join Student_Source as ss on st.Sno=ss.Sno，左外部联接后，结果如下图：

　

　　（2）执行下面SQL语句：

1 2 3 4 5 6

MERGE INTO Student_Target AS st USING Student_Source AS ss ON st.Sno = ss.Sno     WHEN NOT MATCHED BY SOURCE         THEN delete ;

　　最后Student_Target的运行结果为：

 

　　删除了Student_Target中有而Student_Source中没有的行。

　　（2）执行insert和update语句同样报错。

四、最后总结

　　很佩服你竟然能看到这里，最后来一个总结吧：

（1）when matched:类比inner join去思考，可以执行update和delete操作，无法执行insert操作。

（2）when not matched by target：类比right outer join去思考，可以执行insert操作，无法执行update和delete操作。（target表中没有（not matched），而source表中有）

（3）when not matched by source，类比left outer join去思考，可以执行delete 操作，无法执行insert和update操作。（target表中有，而source表中没有(not matched)）

 

 

备注：练习过程中用到的代码：

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

USE testDB GO MERGE INTO Student_Target AS st USING Student_Source AS ss ON st.Sno = ss.Sno     --WHEN MATCHED         --THEN UPDATE SET st.Sname = ss.Sname         --THEN  INSERT VALUES(ss.sno,ss.sname)     --WHEN NOT MATCHED BY TARGET         --THEN UPDATE set st.sname=ss.sname     --WHEN NOT MATCHED BY SOURCE         --THEN UPDATE set st.Sname=ss.Sname,st.Sno=ss.Sno       ;   TRUNCATE TABLE dbo.Student_Target   TRUNCATE TABLE dbo.Student_Source   insert into Student_Target (sno,sname)values(1,'aa'),(2,'bb'),(3,'cc')   insert into Student_Source (sno,sname)values(1,'xiaoming'),(2,'xiaoli'),(4,'xiaohong'),(5,'xiaoping')   SELECT * FROM dbo.Student_Target   SELECT * FROM dbo.Student_Source     SELECT * FROM dbo.Student_Target AS st INNER JOIN dbo.Student_Source AS ss ON st.Sno=ss.Sno   SELECT * FROM dbo.Student_Target AS st RIGHT OUTER JOIN dbo.Student_Source AS ss ON st.Sno=ss.Sno     SELECT * FROM dbo.Student_Target AS st LEFT OUTER JOIN dbo.Student_Source AS ss ON st.Sno=ss.Sno