not in\in与not exists\exists—SQLCookbook

当我们想要从一个表中查找出另一表没有的值时，会使用使用下面的语句：

            select  deptno

                   from dept

           where deptno not in (select deptno from emp)；

                  

但是在这个时候我们要注意NUll值的情况。为了说明情况，我们建立下面的表

          create table new_dept(deptno integer);

          insert into new_dept values (10);

          insert into new_dept values (50);

         insert into new_dept values (null);

       

当使用下列语句时，你会发现没有返回结果。

         select  deptno

           from dept

         where deptno not in (select deptno from  new_dept)；

原因是 in 和 not in 本质上是 OR运算，而在SQL中，“true or null”和"false or null"的结果都是null。所以当结果中有一个null值时，null就会延续下去。上面的语句就等价于：

          select  deptno

           from dept

         where  not (deptno = 10 or  deptno = 50 or deptno = null)；

在这种情况下，not (deptno = 10 or  deptno = 50 or deptno = null)------>(false or false or null)----->null

要想解决这个问题需要采用not exists和相关子查询，正确的写法应该是

           select  d.deptno

           from dept d

         where  not exists (select null from  new_dept n where d.deptno = n.deptno)；