当使用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》