“NOT_IN”与“NULL”的邂逅

3）使用“外连接”改写
 

　　 X
----------
　　　　 2

道理是相通的，想想为什么使用“NOT EXISTS”和“外连接”方法改写后就可以成功？（给大家一个机会，这里不赘述。）

6.小结
这里描述的的案例可以用“陷阱”来形容，不过对于这个“陷阱”我们是有诸多解决方案进行规避的。
遇到问题在沉着、冷静+淡定之后，终有柳暗花明之时。希望您也有思考之后豁然开朗之悦。

Good luck.

secooler
10.02.09

-- The End --

=========================================

IN & NOT IN & EXISTS & NOT EXISTS & TABLE JION
当子查询有返回null值时，not in不会有结果返回，如：

SQL> select ename from emp where deptno not in(10,20,null)
2 /

no rows selected

原因是：

deptno not in(10, 20, null) ===>

not (deptno in(10, 20, null)) ===>

not (deptno=10 or deptno=20 or deptno=null) ===>

deptno<>10 and deptno<>20 and deptno<>null ===>

deptno<>10 and deptno<>20 and unkown ===>

deptno<>10 and deptno<>20 and false ===>false

下面是两个在NOT IN中使用子查询的例子，先看没有null的情况：

SQL> select ename, deptno from emp;

ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10

14 rows selected.

SQL> select dname from dept where deptno not in(select deptno from emp);

DNAME
--------------
OPERATIONS

再看子查询包含null值的情况：

SQL> insert into emp(empno, ename,deptno)
2 values(9999,'LAW',null)
3 /

1 row created.

SQL> select ename, deptno from emp;

ENAME DEPTNO
---------- ----------
LAW
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10

15 rows selected.
SQL> select dname from dept where deptno not in(select deptno from emp);

no rows selected

SQL> select dname from dept
2 where deptno not in(select deptno from emp where deptno is not null)
3 /

DNAME
--------------
OPERATIONS

而子查询包含null时，用in却不会有问题：

SQL> select ename,deptno from emp where deptno in(10,20,null)
2 /

ENAME DEPTNO
---------- ----------
SMITH 20
JONES 20
CLARK 10
SCOTT 20
KING 10
ADAMS 20
FORD 20
MILLER 10

8 rows selected.

IN一般可以改为EXISTS：

SQL> select dname from dept
2 where deptno in(select deptno from emp)
3 /

DNAME
--------------
RESEARCH
SALES
ACCOUNTING

SQL> select dname from dept
2 where exists(select * from emp where emp.deptno=dept.deptno)
3 /