http://www.xifenfei.com/2108.html

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

ITPub博客

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

最新文章