SQLServer数据集合的交、并、差集运算

SQLServer2005通过intersect,union,except和三个关键字对应交、并、差三种集合运算。
 
他们的对应关系可以参考下面图示
 

       相关测试实例如下：

 usetempdb
go
if (object_id ('t1' ) isnotnull ) droptablet1
if (object_id ('t2' ) isnotnull ) droptablet2
go
createtablet1 (aint )
insertintot1select1unionselect2unionselect3
createtablet2 (aint )
insertintot2select3unionselect4unionselect5
go
select*fromt1unionselect*fromt2
go
/* 求表并集
1
2
3
4
5*/
select*fromt1unionall   select*fromt2
go
/*求表并集不过滤重复
1
2
3
3
4
5*/
select*fromt1exceptselect*fromt2
go
/*求t1对t2的差集
1
2*/
select*fromt1intersectselect*fromt2
go
/*求t1对t2的交集
3*/

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/jinjazz/archive/2009/09/07/4527863.aspx