EXISTS 与 IN 的 比较
Exists：
根据子查询是否返回行，该谓词返回true或false，不同于其他谓词和逻辑表达式，无论输入子查询是否返回行，Exists都不会返回unknown。如果子查询的筛选器为某一行返回unknown，则该行不返回。在筛选器中，unknown被认为类似于false，换句话说，当输入子查询包含一个筛选器时，只要该筛选器至少为一行得到true，则Exists将返回true。
在Exists谓词的子查询中，Exists只关心行是否存在，不关心特定属性，优化器将忽略子查询中的select列表。
在SQL SERVER 2000之前的版本,两者其实是有区别的,优化器会为他们生成不同的执行计划,而且EXISTS的性能更好,因为它具有短路功能.在SQL SERVER 2000及之后的版本,优化器通常会为两逻辑等价的查询生成相同的计划.
大家意识到EXISTS和IN有区别,争议之处都集中在SQL的三值逻辑(true,false,unknown)上,不同于EXISTS,当输入列表包含NULL时,IN实际上回产生一个UNKNOWN逻辑结果.例如: IN(b,c,NULL)的结果是UNKNOWN.然而,因为在筛选器中UNKNOWN与FALSE的处理方式类似,使用IN和EXISTS查询的结果是一样的,所以优化器会产生相同的执行计划.
接下来来比较一下NOT EXISTS和NOT IN是否也一样呢?先看NOT EXISTS的情况:
1.SELECT Customer,Customer_Description
2.FROM dbo.Customers As C
3.WHERE Country='CHINA'
4.AND NOT EXISTS
5.(
6. SELECT * FROM Orders As O
7. WHERE O.Customer=C.Customer
8.)
假设Orders表中包含一个Customer为NULL的订单,但它与我们无关,查询依然会得到所有来自CHINA,暂时没有订单的消费者.
该计划扫描Customers表并筛选来自CHINA的消费者.对于每个匹配的消费者,该计划对Orders.Customer 上的索引执行一次查找.在执行计划中会有一个TOP运算符,因为只需确定是否至少有一个订单与该消费者匹配.这就是EXISTS的短路功能.当Orders.Customer列的密度比较大(即包含大量重复),使用TOP特别高效.每个消费者只发生一次查找(Seek),只在叶级(索引的最底层)扫描一行,以查找一个而不是所有的匹配.
如果用NOT IN来解决相同的问题,SQL你可能会写成:
1.SELECT Customer,Customer_Description
2.FROM dbo.Customers As C
3.WHERE Country='CHINA'
4.AND Customer NOT IN(SELECT Customer FROM dbo.Orders)
尝试在Orders表中插入一条Customer为NULL的记录,你会发现,用NOT IN的语句返回空集,因为当Orders.Customer列包含NULL时,IN查询永远不会返回FALSE!!,而是返回TRUE和UNKNOWN,所以NOT IN只返回NOT TRUE或NOT UNKNOWN,不返回TRUE(这句是关键).
下面举例来解释,还是刚才的例子,假设Orders列表是(a,b,NULL), a IN (a,b,NULL)返回TRUE,那么a NOT IN (a,b,NULL)就返回 NOT TRUE,即FALSE,所以查询不返回a, 那么, c IN (a,b,NULL)时,逻辑结果是UNKNOWN,那么 c NOT IN (a,b,NULL)则返回 NOT UNKNOWN,还是UNKNOWN.所以c也不返回值,就是说,无论Customers表中的值是什么,只要Orders表中的Customer中有NULL值,该查询实际上不会返回任何结果.所以NOT EXISTS和NOT IN不是逻辑等价的.所以他们的执行计划是不一样的,返回的结果也会不一样.
在这种情况下,我们只要在NOT IN子查询中加一个筛选器排除NULL值就可以了,如上面SQL中,改为SELECT Customer FROM dbo.Orders WHERE Customer IS NOT NULL.
当然,在实际运用中,大家都不可能允许Orders表中的Customer列是NULL值的.但大家还是要注意,在使用NOT IN的时候,要保证子查询不存在NULL值!!
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/Edwingu/archive/2008/11/12/3286146.aspx
