本篇只讨论in/exists和not in/not exists语意，不涉及这些写法的执行效率问题，至于效率问题请见:in/exists和not in/not exists执行效率
1、准备实验环境
C:\Users\XIFENFEI>sqlplus chf_xff/xifenfei
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 12月 10 14:55:14 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create table t1 (c1 number,c2 number);
表已创建。
SQL> create table t2 (c1 number,c2 number);
表已创建。
SQL> insert into t1 values (1,2);
已创建 1 行。
SQL> insert into t1 values (1,3);
已创建 1 行。
SQL> insert into t2 values (1,2);
已创建 1 行。
SQL> insert into t2 values (1,null);
已创建 1 行。
SQL> insert into t1 values (1,null);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from t1;
C1 C2
---------- ----------
1 2
1 3
1
SQL> select * from t2;
C1 C2
---------- ----------
1 2
1
Note:t1和t2表都有null，且t1比t2多一条记录
2、t2做内部表
SQL> select * from t1 where c2 in (select c2 from t2 );
C1 C2
---------- ----------
1 2
SQL> select * from t1 where exists (select c2 from t2 where t1.c2=t2.c2);
C1 C2
---------- ----------
1 2
SQL> select * from t1 where c2 not in (select c2 from t2 );
未选定行
SQL> select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);
C1 C2
---------- ----------
1 3
1
3、t1为内部表
SQL> select * from t2 where c2 in (select c2 from t1 );
C1 C2
---------- ----------
1 2
SQL> select * from t2 where exists (select c2 from t1 where t1.c2=t2.c2);
C1 C2
---------- ----------
1 2
SQL> select * from t2 where c2 not in (select c2 from t1 );
未选定行
SQL> select * from t2 where not exists (select 1 from t1 where t1.c2=t2.c2);
C1 C2
---------- ----------
1
3、结论
in和exists结果相同(都会排除掉null,无论内部表中有无null)
not in会过滤掉外部表中的null(即使内部表中无null)
not exists不会过滤掉外部表的null(即使内部表有null)
由于篇幅关系,括号中的部分实验过程未展现出来
