ITPub博客

首页 > Linux操作系统 > Linux操作系统 > in 和 not in ,exists 和 not exists

in 和 not in ,exists 和 not exists

原创 Linux操作系统 作者:shuangoracle 时间:2012-05-27 08:31:00 0 删除 编辑
昨天听崔华讲 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-08-25

  • 博文量
    84
  • 访问量
    206964