EXISTS 与 IN 的 比较

     曾经有位同学问过我这个问题,EXISTS和IN的区别,我当时给他的答复是:执行计划相同,没有区别.现在回想起来,太片面,觉得有必要整理一下两者的异同.
    在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值!!
希望Eric Huang同学满意我的回答.