in 和 not in ，exists 和 not exists

昨天听崔华讲 in 和 not in 不完全相反，回来做了下测试。同时对比了下 exists 和 not exists。

测试环境：

create table t1(id number,name varchar2(20));

insert into t1 values(1,'A');

insert into t1 values(2,'B');

insert into t1 values(3,'');

create table t2(id number,name varchar2(20));

insert into t2 values(1,'A');

查看表里值的分布情况：

SQL> select id,name from t1;

        ID NAME

---------- ----------------------------------------

         1 A

         2 B

         3

SQL> select id,name from t2;

        ID NAME

---------- ----------------------------------------

         1 A

下面看下执行结果：

1、 in 和 not in

--in，和我们猜测一样 只有 1  A 吧

SQL> select id,name from t1 where name in (select name from t2);

        ID NAME

---------- ----------------------------------------

         1 A

--not in，我们最初的猜测可能是除了 1   A外所有的吧，那看下结果，让你失望了吧

SQL> select id,name from t1 where name not in (select name from t2);

        ID NAME

---------- ----------------------------------------

         2 B

--如果in后括号里有null值又会怎样？

SQL> select id,name from t1 where name not in ('A',null);

未选定行

从上面看出in后面的括号里只要有null，就会一条都取不出来。本来猜想应该是2 B，但是它不知道null和B是否相等啊，那干脆一条都不出来了

这里还是用到了 null 的特殊性。null 是 unknown，是不确定的值。当和别的值比较时，因为它的不确定性，使用in的时候它不知道null是多少，

使用not in时亦不知道null值是多少，导致in和not in都将这一行给筛掉。

2、 exists 和 not exists

还是上面的表和数据：

SQL> select id,name from t1 where exists (select t2.name from t2 where t2.name=t1.name);

        ID NAME

---------- ----------------------------------------

         1 A

SQL> select id,name from t1 where not exists (select t2.name from t2 where t2.name=t1.name);

        ID NAME

---------- ----------------------------------------

         3

         2 B

再看用not in 的相关子查询：

SQL> select id,name from t1 where t1.name not in (select t2.name from t2 where t2.name=t1.name);

        ID NAME

---------- ----------------------------------------

         2 B

         3

上面这三例：not in 和 not exists 用子查询时效果是一样的，not exists和not in并不总是可以替换的。

从上面的讨论可以看出：

in不一定是not in的反面，而exists一定是not exists的反面。in可以用exists替换，但是not in不一定可以用not exists替换。

总结下null：

1、null可以用nvl函数替换

2、null和任何值直接加减乘除都会得null

3、当表中一个字段中有null时，求sum会将null过滤掉，只求其他非null字段的和

4、null做比较时只能用is null或is not null

5、索引不会存储完全为空值的索引键。当表索引允许为空值时，可以用count(*)来得到表总行数，切不可以用count(索引键)，因为这样会少行数

6、升序排序在最底端，降序排序在最上端

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24496749/viewspace-731051/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24496749/viewspace-731051/