SQL2005学习笔记 EXCEPT和INTERSECT运算符

1.简介
except和intersect运算符使您可以比较两个或多个select语句的结果并返回非重复值。
2.区别
except运算符返回由except运算符左侧的查询返回、而又不包含在右侧查询所返回的值中的所有非重复值。
intersect返回由intersect运算符左侧和右侧的查询都返回的所有非重复值。
3.注意事项
(1).使用except或intersect比较的结果集必须具有相同的结构。它们的列数必须相同，并且相应的结果集列的数据类型必须兼容
(2).intersect运算符优先于except
(3).select into必须是包含intersect或except运算符的语句中的第一个查询，用来创建容纳最终结果集的表
(4).order by子句中的列名或别名必须引用左侧查询返回的列名
4.例题：

--建立3个表，分别插入数据
create table tablea(col1 int)
insert into tablea select 1
insert into tablea select 1
insert into tablea select 2
insert into tablea select 3
insert into tablea select 4
insert into tablea select 4
insert into tablea select 5
insert into tablea select null
insert into tablea select null

create table tableb(col2 int)
insert into tableb select null
insert into tableb select 1
insert into tableb select 2
insert into tableb select 3

create table tablec(col3 int)
insert into tablec select 1
insert into tablec select 5
insert into tablec select 6


--利用except
--找出tablea表的col1列不存在tablec表col1列的所有非重复值
select col1 from tablea
except
select col3 from tablec

结果如下：
col1
-----------
null
2
3
4


--sql 2000的版本，用not exists实现except的功能
select col1
from tablea as a
where not exists(select col3 from tablec where a.col1=col3)
group by col1

--sql 2000,not in是得不到上述结果的
--空值表示值未知。空值不同于空白或零值。没有两个相等的空值。
--比较两个空值或将空值与任何其他值相比均返回未知，这是因为每个空值均为未知。
--使用in或not in比较后返回的所有空值都将返回unknown。
--将空值与in或not in一起使用会产生意外结果。
select col1
from tablea
where col1 not in(select col3 from tablec)
group by col1

结果如下：
col1
-----------
2
3
4


--intersect运算符优先于except
--运算步骤是：先运算tableb和tablec的intersect,再和tablea运算except
select col1 from tablea
except
select col2 from tableb
intersect
select col3 from tablec

结果如下：
col1
-----------
null
2
3
4
5


--select into的应用
--select into必须是语句中的第一个查询
--我记得select into与union运算符的使用也是这样的规则
select col1
into #tem
from tablea
except
select col3
from tablec

select * from #tem
drop table #tem

结果如下：
col1
-----------
null
2
3
4


--order by子句
--order by子句中的列名或别名必须引用左侧查询返回的列名
select col1 from tablea
intersect
select col3 from tablec
order by col1

结果如下：
col1
-----------
1
5