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
union
select
union
select
create
table
t2
(a
int
)
insert
into
t2
select
union
select
union
select
go
select
*
from
t1
union
select
*
from
t2
go
/* 求表并集
5*/
select
*
from
t1
union
all
select
*
from
t2
go
/*求表并集不过滤重复
5*/
select
*
from
t1
except
select
*
from
t2
go
/*求t1对t2的差集
2*/
select
*
from
t1
intersect
select
*
from
t2
go
/*求t1对t2的交集
3*/