SQL Server 2005中的except/intersect和outer apply

傷城~ 2021-11-29 15:38 300阅读 0赞

如果您在不太关注性能问题的情况下，尽可能的使用简介的sql语句是提高工作效率的一个有效办法，上一篇blog提到了[使用coalesce和nullif的组合来减轻写sql的工作量][coalesce_nullif_sql] 的方法，这篇blog将通过实现一个逻辑来讲3个sql server 2005后提供的新方法。  
  
首先，建立两个表：

![Image 1][] CREATE   TABLE  \#a (ID  INT )   
![Image 1][] INSERT   INTO  \#a  VALUES  ( **1** )   
![Image 1][] INSERT   INTO  \#a  VALUES  ( **2** )   
![Image 1][] INSERT   INTO  \#a  VALUES  ( null )   
![Image 1][]  
![Image 1][] CREATE   TABLE  \#b (ID  INT )   
![Image 1][] INSERT   INTO  \#b  VALUES  ( **1** )   
![Image 1][] INSERT   INTO  \#b  VALUES  ( **3** )

我们的目的是从表\#b中取出ID不在表\#a的记录。  
如果不看具体的insert的内容，单单看这个需求，可能很多朋友就会写出这个sql了：

![Image 1][] select   \*   from  \#b  where  id  not   in  ( select  id  from  \#a)

但是根据上述插入的记录，这个sql检索的结果不是我们期待的ID=3的记录，而是什么都没有返回。原因很简单：在子查询select id from \#a中返回了null，而null是不能跟任何值比较的。  
  
那么您肯定会有下面的多种写法了：

![Image 1][] select   \*   from  \#b  where  id  not   in  ( select  id  from  \#a  where  id  is   not   null )  
![Image 1][] select   \*   from  \#b b  where  b.id  not   in  ( select  id  from  \#a a  where  a.id = b.id)  
![Image 1][] select   \*   from  \#b b  where   not   exists  ( select   **1**   from  \#a a  where  a.id = b.id)

当然还有使用left join/right join/full join的几种写法，但是无一例外，都是比较冗长的。其实在SQL Server 2005增加了一种新的方法，可以帮助我们很简单、很简洁的完成任务：

![Image 1][] select   \*   from  \#b  
![Image 1][] except   
![Image 1][] select   \*   from  \#a

我不知道在SQL Server 2008里还有没有什么更酷的方法，但是我想这个应该是最简洁的实现了。当然，在2005里还有一种方法可以实现：

![Image 1][] select   \*   from  \#b b  
![Image 1][] outer  apply  
![Image 1][]( select  id  from  \#a a  where  a.id = b.id) k  
![Image 1][] where  k.id  is   null

outer apply也可以完成这个任务。  
  
如果我们要寻找两个表的交集呢？那么在2005就可以用intersect关键字：

![Image 1][] select   \*   from  \#b  
![Image 1][] intersect   
![Image 1][] select   \*   from  \#a  
![Image 1][]  
![Image 1][]ID  
![Image 1][] \-- \---------   
![Image 1][] **1**   
![Image 1][]  
![Image 1][]( **1**  row(s) affected)

转载于:https://www.cnblogs.com/fanweixiao/archive/2008/03/30/1129482.html

[coalesce_nullif_sql]: http://www.cnblogs.com/fanweixiao/archive/2008/03/30/1129477.html
[Image 1]: