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 

上一个：SQLServer 2005 列所有存储过程的语句
下一个：SQL Server 2005 同步复制技术