维生素C.net

IronRuby, CLR/DLR, Debugging
posts - 75, comments - 212, trackbacks - 10, articles - 0
  博客园 :: 首页 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理

SQL Server 2005中的except/intersect和outer apply

Posted on 2008-03-30 06:35 new 维生素C.net() 阅读(390) 评论(1)  编辑 收藏 所属分类: SQL Server

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

首先，建立两个表：

CREATE TABLE #a (ID INT) 
INSERT INTO #a VALUES (1) 
INSERT INTO #a VALUES (2) 
INSERT INTO #a VALUES (null) 

CREATE TABLE #b (ID INT) 
INSERT INTO #b VALUES (1) 
INSERT INTO #b VALUES (3) 

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

select * from #b where id not in (select id from #a)

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

那么您肯定会有下面的多种写法了：

select * from #b where id not in (select id from #a where id is not null)
select * from #b b where b.id not in (select id from #a a where a.id=b.id)
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增加了一种新的方法，可以帮助我们很简单、很简洁的完成任务：

select * from #b
except
select * from #a

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

select * from #b b
outer apply
(select id from #a a where a.id=b.id) k
where k.id is null

outer apply也可以完成这个任务。

如果我们要寻找两个表的交集呢？那么在2005就可以用intersect关键字：

select * from #b
intersect
select * from #a

ID
-----------
1

(1 row(s) affected)

标题  
姓名  
主页
Email (博主才能看到) 
验证码 *  看不清,换一张 [登录][注册]
内容(请不要发表任何与政治相关的内容)  
网站首页

新闻频道

社区

小组

博问

网摘

闪存

找找看
  登录  使用高级评论  新用户注册  返回页首  恢复上次提交      


相关链接:

最新IT新闻:
AMD剥离芯片制造业务 获60亿美元投资
Google向Wine贡献更多代码
苹果创始人Steve Wozniak称iPod将被淘汰
福布斯：硅谷日子将更难过 谁下一个倒下
微软老矣 尚能赚否？