Sql Server2005 Transact-SQL 新兵器学习总结之-EXCEPT和IN

    
  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