昨天听崔华讲 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/