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

javenpop 2015-04-09

展开全文

SQLServer2005通过intersect,union,except和三个关键字对应交、并、差三种集合运算。

他们的对应关系可以参考下面图示

相关测试实例如下：

 use
 tempdb


 go


 if
(object_id
('t1'
) is
 not
 null
) drop
 table
 t1


 if
(object_id
('t2'
) is
 not
 null
) drop
 table
 t2


 go


 create
 table
 t1
 (a
 int
)

 insert
 into
 t1
 select
 1
 union
 select
 2
 union
 select
 3


 create
 table
 t2
 (a
 int
)

 insert
 into
 t2
 select
 3
 union
 select
 4
 union
 select
 5


 go


 select
 *
 from
 t1
 union
 select
 *
 from
 t2


 go


 /* 求表并集


 1


 2


 3


 4


 5*/


 select
 *
 from
 t1
 union
 all
  select
 *
 from
 t2


 go


 /*求表并集不过滤重复


 1


 2


 3


 3


 4


 5*/


 select
 *
 from
 t1
 except
 select
 *
 from
 t2


 go


 /*求t1对t2的差集


 1


 2*/


 select
 *
 from
 t1
 intersect
 select
 *
 from
 t2


 go


 /*求t1对t2的交集


 3*/