not in 和not exists的使用

        当使用not in 子句时，一定要注意null值问题。 考虑下面的表new_dept:

SQL>  create table new_dept(deptno integer);

Table created.

SQL> insert into new_dept values(10);

1 row created.

SQL> insert into new_dept values(50);

1 row created.

SQL> insert into new_dept values(null);

1 row created.

SQL> select * from new_dept;

    DEPTNO
----------
        10
        50

       如果使用子查询和not in子句，来查找表dept中所有在new_dept表中没有的deptno值，将会发现返回的查询结果是空的。

SQL> select distinct deptno from dept;

    DEPTNO
----------
        10
        20
        30
        40

SQL> select * from dept where deptno not in (select deptno from new_dept);

no rows selected

        deptno值为20、30和40的记录在表new_dept并不存在，但这个查询没有返回结果。原因就是在表new_dept中有一个空值。

        in等价于or操作，not in等价于not or，考虑：

      

SQL> select deptno from dept where deptno not in (10,50,null);

no rows selected

SQL> select deptno from dept where not (deptno=10 or deptno=50 or deptno=null);

no rows selected

SQL>

          在这种情况下，当deptno=20时，表达式的输出为：

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

(false or false or null)

(false or null)

null

 

      在SQL中，“true or null”的结果就是true，而“false or null”的结果是null！

 

      解决与not in 和null有关的问题，可以使用 not exists和相关子查询。

 

SQL>  select deptno from dept d where not exists( select 1 from new_dept n where d.deptno=n.deptno);

    DEPTNO
----------
        30
        40
        20

SQL>

 

      exists/not exists 与相关子查询一起使用的时候，子查询select列表中的项目并不重要，因此，这里选择1  （null也行），重点在联接子查询上，而不是select列表的项目。

 

 

整理自《SQL Cookbook》