in/exists和not in/not exists语意探讨

本篇只讨论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)
由于篇幅关系,括号中的部分实验过程未展现出来