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