一段纠结sql

从表中选取不在另一张表中的记录的方法(个人总结)
http://bbs.chinaunix.net/viewthread.php?tid=821114
我转的，原来的地址，写上来，呵呵
前2天把那本sql cookbook拿出阿里复习了下，发现还有很多很多东西没能明白，这就是其中之一了。貌似这个解释的要好多了，书上好像有错。
从表中选取不在另一张表中的记录的方法

    假设我们想从A表中选择一些记录，记录中的部分字段的取值是B表
所不存在的，这里定义A表为源表，B表为参照表。例如，常见的例子
部门表(Department)作为源表，员工表(Employee)作为参照表，可以
从部门表中列举出那些员工表中所不包含的部门id来，即找出那些没有
员工的部门。
    一般类来说，如果数据库支持集合操作符，就可以直接查询，如果
没有，就使用子查询。
(1)
DB2 and PostgreSQL:
select deptno from dept
except
select deptno from emp
(2)
Oracle and Teradata:
select deptno from dept
minus
select deptno from emp
(3)
MySQL and SQL Server:
select deptno
  from dept
where deptno not in (select deptno from emp)

    对于(1)和(2)没有什么多说的，就是需要注意满足集合操作符使用的一般
条件就可以了。而且返回的结果是不重复的(duplicate removed)。所以对于3
使用者如果对结果重复性有要求的话，就需要自己根据情况添加是否使用
Distinct关键词。

    另外一个很显著的区别在于参照表中进行比较的字段是否包含NULL。这个
问题对于集合操作符来说没有任何影响，DBMS本身实现操作符的时候已经解决了。
而使用not in的子查询方式就需要采取处理措施。
    例如：源表dept中deptno取值有10,20,30,40
          参照表new_dept中deptno取值有10,50,null
         
SQL A:
     select a.deptno from dept a where a.deptno
         not in (select deptno from new_dept)
    虽然源表中20,30,40这三个deptno都不存在于参照表中，但是上面这个SQL
的查询结果为空，一条记录也没有。
而想要得到需要的结果需要写成：SQL B
     select a.deptno from dept a where a.deptno
         not in (select deptno from new_dept where deptno is not null)
查询结果为 20,30,40三条记录

SQL A之所以没有任何结果的原因在于NULL, NOT,和OR的几个操作符的综合作用
首先IN操作符等价多个OR的并列，所以SQL A实际上等价为
     A-1:
          select a.deptno from dept a where a.deptno
         not in(10,50,null)
     A-2:
          select deptno from dept where
         not  (deptno=10 or deptno=50 or deptno=null)
下面要注意，必须明确几个操作符之间的结果：
  TRUE or NULL   结果是TRUE
  FALSE or NULL  结果是NULL
 
NOT的运算结果
   bool表达式     NOT 运算结果
     TURE           FALSE
     FALSE           TRUE
     NULL            NULL
尤其注意NOT对未知的字段运算结果还是NULL，这一点一定要和
IS NULL 和IS NOT NULL这个关键词组合区分开。

最后任何字段和NULL进行运算=,+,-,...结果都是NULL
这也是SQL里面强调用IS NULL和IS NOT NULL来区分字段是否为空
而不是用＝来区分

明确了上面运算符的运算规则，我们来看
not  (deptno=10 or deptno=50 or deptno=null)
当源表中deptno为10时，则
not ( TRUE or FALSE or NULL )
结果是 not (TRUE) -> FALSE  这条记录不会显示
当源表中dept为20时， 则
not ( FALSE or FALSE or NULL )
结果是 not (NULL) -> NULL  
where的搜索条件是空，也不会有任何结果
(你可以尝试select * from dept where deptno=null这种sql看看结果)

综上，无论源表的记录取何值，最终都得不到查询结果。。。

如果想要得到结果，必须写成SQL B的格式。

如果觉得始终要为总要当心字段是否为空，而不得不在SQL中注意比较麻烦的情况下
(比如在复杂的查询中，源表和参照表之间需要比较的字段比较多，每个字段都要
考虑NOT NULL，并且书写出来，确实比较繁琐)，可以使用相关子查询
(correlated subquery)来巧妙的避免这个干扰

SQL C:
        select *
          from dept a
         where not exists (select null from new_dept b where b.deptno =a.deptno )
这个查询同样能够得到需要的结果

Step 1:   子查询中执行b.deptno =a.deptno 两个表join操作
Step 2:   如果子查询返回结果，即子查询包含行，则exists(subquery)返回TRUE，
          而not exists(subquery)返回FALSE，最终查询抛弃当前记录
Step 2:   如果子查询没有结果，即子查询不包含任何行，则exists(subquery)返回FALSE，
          而not exists(subquery)返回TRUE，最终查询返回当前记录
         
这里需要注意的是在使用exists情况下，其子查询中的select部分并不重要。
因为exists的含义是exists(subquery),只要子查询中包含行，无论是什么样的行记录，
结果就是TURE而SQL C中的subquery 中只要b.deptno和a.deptno相等，就会有行记录，
而select null from new_dept b where b.deptno =a.deptno 只不过是说两个deptno相等
返回的行记录是null(null也是一条行记录啊)，所以exists(select null)也是TURE
因此，这种写法就保证了，只要源表中的deptno记录不存在在参考表中，就能够得到结果了。

子查询中的select对结果不会有任何影响，所以写成select null好像是一种惯例，
我觉得是为了强调相关查询的等价条件而已，但这种符合英语国家的惯例对很多
初次遇到这种写法的人来说，反而被迷惑了一把。。。

只要把握了几个关键的操作符的运算规则，就能够理解这些不同写法所造成的不同结果了。
希望上面的总结能够对大家理解这种查询方式有所帮助。


至于效率方面，exists是否快于in操作，个人持保留意见，这主要看具体的SQL书写条件
和DBMS系统的优化代码。

但是上面的语句SQL C比SQL B的执行效率要高，这一点是可以解释的。
SQL B使用in 模式，这样实际上是全表扫描，没有任何优化手段，两个表都在遍历全表
而SQL C使用相关查询，两个表是内联操作，大多数数据库系统都会使用Hash Join来
进行优化查询，所以效率会比较高。


总结了这么多，但很多时候由于不同的数据库平台对同样的SQL语句都有不同的优化策略，
所以大家可以把SQL语句用目标平台的执行计划工具查看一下就可以知道大概的执行流程
对理解SQL也有很好的帮助。

有什么问题也希望大家一起讨论。呵呵，希望我的总结没有把大家绕晕....

现在的数据库技术发展的也很迅速，可能有更好的办法来实现这种查询方式，本人能力
有限，所以有更好的方法，或者不同的见解，欢迎一起讨论

flycoco @ 2006-08-31