关于NOT IN运算符的一些特殊之处

文档地址：http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref502

IN Operator

The IN operator tests set membership.It means "equal to any member of." The set can contain nulls, butthey are ignored. For example, the following expression testswhether a value is part of a set of values:

letter IN ('a','b','c')

 

 

Be careful when inverting this condition. Expressions ofthe form:

value NOT IN set

yield FALSE if the set contains anull.--如果这集合（set）包括了空值，那么这个表达式将产生FALSE。 例如：

 

select dest,revenue from test where revenue not in(
select 4000 from test union select null fromdual);--此结果将返回0行。

 

对于子查询：select 4000 from test union select null from dual来说，这个结果集有NULL值。

而且使用运算符NOT IN。因此WHERE 条件为FALSE。因此不会返回的行。