首页 > 数据库 > Oracle > 正文
Oracle Null与in,exists的关系说明(not in 查不到结果)
2011-09-27       个评论      
收藏    我要投稿

  同事说查询遇到一个奇怪的事,2个表进行not in 操作没有返回结果,正常情况下应该是有返回的。
 
一.问题重现
一般来说,问题能重现就是好消息,最怕不能重现。
 
SQL> connscott/tiger;
Connected.
SQL> descemp
 Name                                     Null?    Type
 ------------------------------------------------- -----------------
 EMPNO                                     NOT NULLNUMBER(4)
 ENAME                                             VARCHAR2(10)
 JOB                                               VARCHAR2(9)
 MGR                                               NUMBER(4)
 HIREDATE                                           DATE
 SAL                                               NUMBER(7,2)
 COMM                                              NUMBER(7,2)
 DEPTNO                                            NUMBER(2)
 
将emp 表复制一份:
SQL> createtable emp1 as select * from emp;
Table created.
 
我们向emp 表里插入一些值:
SQL> insertinto emp(empno,ename) values(8888,'Dave');
1 row created.
SQL>commit;
Commitcomplete.
 
这里我们只插入了empno和ename,其他为空。
 
下面进行2张表的的操作:
 
SQL> selectempno,ename from emp where job not in (select job from emp1);
no rowsselected
--这里没有返回结果集,正常情况下应该反回我们之前insert 的dave。
 
SQL> selectempno,ename from emp where job  in(select job from emp1);
 
     EMPNO ENAME
--------------------
      7934 MILLER
      7900 JAMES
      7876 ADAMS
      7369 SMITH
      7844 TURNER
      7654 MARTIN
      7521 WARD
      7499 ALLEN
      7782 CLARK
      7698 BLAKE
      7566 JONES
 
     EMPNO ENAME
--------------------
      7902 FORD
      7788 SCOTT
      7839 KING
 
14 rowsselected.
 
 
换成exists 进行测试:
SQL>select  empno,ename     from emp A where  not  exists  ( SELECT * FROM emp1 Bwhere B.job = A.job);
 
     EMPNO ENAME
--------------------
      8888 Dave
 
SQL>select   empno,ename   from emp A where  exists ( SELECT * FROM emp1 B where B.job = A.job);
 
     EMPNO ENAME
--------------------
      7934 MILLER
      7900 JAMES
      7876 ADAMS
      7369 SMITH
      7844 TURNER
      7654 MARTIN
      7521 WARD
      7499 ALLEN
      7782 CLARK
      7698 BLAKE
      7566 JONES
 
     EMPNO ENAME
--------------------
      7902 FORD
      7788 SCOTT
      7839 KING
 
14 rowsselected.
 
使用exists 不受null 的影响。
 
二.问题分析
我们在emp 表里查询的记录有空值,并且我们进行not in 和exists 操作时,都是用null 来判断的,如果我们换成非null 字段就可以正常进行操作了。
 
SQL> selectempno,ename from emp where empno not in (select empno from emp1);
 
     EMPNO ENAME
--------------------
      8888 Dave
 
换成非null 字段就能正常显示了。
 
2.1 Null 说明
 
联机文档上的说明如下:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements005.htm#i59110
 
A condition that evaluates to UNKNOWN acts almost like FALSE.For example, a SELECT statement with a condition in the WHERE clausethat evaluates toUNKNOWN returns no rows. However, a condition evaluatingto UNKNOWN differs from FALSE in that further operations onan UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluatesto TRUE, but NOT UNKNOWN evaluates to UNKNOWN.
 
Table 3-20 shows examples of various evaluations involving nulls inconditions. If the conditions evaluating to UNKNOWN were used in a WHERE clauseof aSELECT statement, then no rows would be returned for that query.
Table 3-20 ConditionsContaining Nulls
Condition
Value of A
Evaluation
a IS NULL
10
FALSE
a IS NOT NULL
10
TRUE
a IS NULL
NULL
TRUE
a IS NOT NULL
NULL
FALSE
a = NULL
10
UNKNOWN
a != NULL
10
UNKNOWN
a = NULL
NULL
UNKNOWN
a != NULL
NULL
UNKNOWN
a = 10
NULL
UNKNOWN
a != 10
NULL
UNKNOWN
 
 
2.2  IN 和 NOT IN 判断说明
IN功能上相当于 =ANY 的操作,而NOT IN 功能上相当于 !=ALL 的操作。
 
IN在逻辑上实际上就是对给定的成员集合或者子查询结果集进行逐条的判定.
如:select * from t1 where id in(1,2,3,NULL);
 
       实际执行的命令等价于:
       Select * from t1 where id=1 or id=2 orid=3 or id=NULL;
     
根据上面的表,Id=NULL 为UNKNOWN。 那么无法查询出列值为Null的记录。即等价于:
 Select * from t1 where id=1or id=2 or id=3
 
 
       NOT IN 的逻辑关系可以理解为:NOT (X=Y OR N=M) 等价于 X!=Y AND N!=M。那么:
select * fromt1 where id not in (1,2,3,NULL);
 
等价于
Select * fromt1 where id !=1 and id!=2 and id !=3 and id !=NULL
 
       根据上面的NULL 表,id!=NULL 的结果为UNKNOWN。 那么该值为假,所以不管前面的条件真假与否,整个逻辑判断为假,所以没有返回任何记录。
 
解决方法就是在in 和not in的操作之前先把NULL 过滤掉。
 
 
2.3 EXISTS 说明
 
先看看exists 的执行过程:
select   *   from  t1  where   exists  (  select * from  t2  where t2.col1 = t1.col1)
 
相当于:
 
for  x  in  (  select   *   from  t1 )
   loop
       if  (  exists  (  select * from  t2  where t2.col1 =  x.col1))
       then
         OUTPUT THE RECORDin x
       end   if
end  loop
 
exists  (  select * from  t2  where t2.col1 =  x.col1)返回是一个布尔值,not exists只是对exists子句返回对布尔值取非,这与in和not in是有本质区别的(not in是对in表达式取非,转换成另一种等价表达式)
 
从上面的逻辑,也可以看出EXISTS语句实际上是通过循环外部查询的结果集,来过滤出符合子查询标准的结果集。于是外部查询的结果集数量对该语句执行性能影响最大,故如果外部查询的结果集数量庞大,用EXISTS语句的性能也不一定就会好很多。

但使用exists 一个很明显的优点,就是如果相关字段有索引的化,会使用索引来进行操作。而不需要进行全表扫描。 当表大的时候,效率肯定是会比in 和not in 高。这也是我们在写SQL 时推荐使用exists的原因。
                      
 
现在看一下我们之前使用not in 查不到结果,但用not exits 却可以查到:
 
SQL>select  empno,ename     from emp A where  not  exists  ( SELECT * FROM emp1 Bwhere B.job = A.job);
 
     EMPNO ENAME
--------------------
      8888 Dave
 
Dave的记录 存在与emp 表,不存在emp1表。
 
我们上边的查询等价于:
for  x  in  (  select   *   from empA )
   loop
       if  (not exists  (  select * from emp2B where B.job =  x.job )
       then
         OUTPUT THE RECORDin x
       end   if
end  loop
 
这样当我们的X.job 为NULL 时,满足条件,输出了Dave 的记录。

作者“David Dai Oracle 笔记”

点击复制链接 与好友分享!回本站首页
相关TAG标签 结果
上一篇:oracle体系结构
下一篇:Oracle彻底优化——优化内存
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站