理解NULL如何影响IN和EXITS语句

原创 2004年10月15日 08:54:00
作者: Builder.com
Thursday, October 14 2004 12:00 PM

本文译自Builder.com,未经许可请勿转载

从表面上看,IN和EXITS的SQL语句是可互换和等效的。然而,它们在处理UULL数据时会有很大的差别,并导致不同的结果。问题的根源是在一个Oracle数据库中,一个NULL值意味着未知变量,所以操作NULL值的比较函数的结果也是一个未知变量,而且任何返回NULL的值通常也被忽略。例如,以下查询都不会返回一行的值:

advertisement_e1.gif
PCPro_MPU.swf PCPro_MPU.swf PCPro_MPU.swf code;sz=1x1;ord=585339979?

select 'true' from dual where 1 = null;

select 'true' from dual where 1 != null;

 

只有IS NULL才能返回true,并返回一行:

select 'true' from dual where 1 is null;

select 'true' from dual where null is null;

 

当你选择使用IN,你将会告诉SQL选择一个值并与其它每一值相比较。如果NULL值存在,将不会返回一行,即使两个都为NULL。

select 'true' from dual where null in (null);

select 'true' from dual where (null,null) in ((null,null));

select 'true' from dual where (1,null) in ((1,null));

一个IN语句在功能上相当于= ANY语句:

select 'true' from dual where null = ANY (null);

select 'true' from dual where (null,null) = ANY ((null,null));

select 'true' from dual where (1,null) = ANY ((1,null));

 

当你使用一个EXISTS等效形式的语句,SQL将会计算所有行,并忽略子查询中的值。

select 'true' from dual where exists (select null from dual);

select 'true' from dual where exists (select 0 from dual where null is null);

 

IN和EXISTS在逻辑上是相同的。IN语句比较由子查询返回的值,并在输出查询中过滤某些行。EXISTS语句比较行的值,并在子查询中过滤某些行。对于NULL值的情况,行的结果是相同的。

selectename from emp where empno in (select mgr from emp);

selectename from emp e where exists (select 0 from emp where mgr = e.empno);

 

然而当逻辑被逆向使用,即NOT IN 及NOT EXISTS时,问题就会产生:

selectename from emp where empno not in (select mgr from emp);

selectename from emp e where not exists (select 0 from emp where mgr =

e.empno);

 

NOT IN语句实质上等同于使用=比较每一值,如果测试为FALSE或者NULL,结果为比较失败。例如:

select 'true' from dual where 1 not in (null,2);

select 'true' from dual where 1 != null and 1 != 2;

select 'true' from dual where (1,2) not in ((2,3),(2,null));

select 'true' from dual where (1,null) not in ((1,2),(2,3));

这些查询不会返回任何一行。第二个查询语句更为明显,即1 != null,所以整个WHERE都为false。然而这些查询语句可变为:

select 'true' from dual where 1 not in (2,3);

select 'true' from dual where 1 != 2 and 1 != 3;

 

你也可以使用NOT IN查询,只要你保证返回的值不会出现NULL值:

selectename from emp where empno not in (select mgr from emp where mgr is not

null);

selectename from emp where empno not in (select nvl(mgr,0) from emp);

 

通过理解IN,EXISTS, NOT IN,以及NOT EXISTS之间的差别,当NULL出现在任一子查询中时,你可以避免一些常见的问题。

 

Scott Stephens已经在Oracle公司工作大于13年之久,他的工作领域包括技术支持,电子商务,市场开发,以及软件开发。



 

理解null如何影响in和exits语句

从表面上看,in和exits的sql语句是可互换和等效的。然而,它们在处理uull数据时会有很大的差别,并导致不同的结果。问题的根源是在一个oracle数据库中,一个null值意味着未知变量,所以操作...
  • aiunong
  • aiunong
  • 2006年04月10日 10:51
  • 1080

[转]理解NULL是如何影响IN和EXITS语句的

理解NULL是如何影响IN和EXITS语句的 [转自]http://www.linuxpk.com/49217.html 从表面上看,IN和EXITS的SQL语句是可互换和等效的。然而,它们在处理UU...
  • RedPea
  • RedPea
  • 2008年01月22日 14:51
  • 481

in、or、exists区别

in 和or区别: 如果in和or所在列有索引或者主键的话,or和in没啥差别,执行计划和执行时间都几乎一样。 如果in和or所在列没有 索引的话,性能差别就很大了。在没有索引的情况下,随着in或...
  • qq_34783818
  • qq_34783818
  • 2017年03月07日 20:04
  • 320

双重NOT EXISTS经典分析

CREATE TABLE  J(JNO VARCHAR(5) NOT NULL PRIMARY KEY,                  JNAME VARCHAR(20) NOT NULL, ...
  • u010416101
  • u010416101
  • 2014年10月23日 14:49
  • 835

sql语句中exists和in用法的区分

初学java两个多月,这两天在整理老师的笔记对数据库中的exists和in的用法分不清楚,从网上搜了些资料,作了点总结。 一、问题起因 起初是由两条语句执行结果不同,语句如下: --分析以下结果...
  • U___U
  • U___U
  • 2015年03月27日 15:25
  • 4499

sql语句查询时is not null 的使用

我今天在两个表关联查询时,发现效率极低,索引也加了,依然如此。例如下面的查询语句: select * from table_group a,table_member b where a.groupId...
  • keketrtr
  • keketrtr
  • 2010年03月31日 18:28
  • 1517

理解SQL语句中 Exists()

理解SQL语句中 Exists() exists对于主查询而言只有一个作用:返回ture或false,而其本身查询的結果集不具任何意义因此在子查询的Select命令语句的字段行中通常使用通用字符*或者...
  • zwzgood
  • zwzgood
  • 2009年06月17日 09:56
  • 2059

mysql中IN和EXITS效率

mysql中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高,这种说法其实是不准确的...
  • CJaver
  • CJaver
  • 2014年10月07日 12:44
  • 1392

orale null 测试 and 关于Oracle中的不等于号

create table testequal (id number,name varchar2(100));   (1)当数据类型为 varchar2时, 插入‘’,相当于null ...
  • z69183787
  • z69183787
  • 2013年03月29日 22:38
  • 2596

关于对编程语言的一点理解

什么是语言标准:就是大家写翻译源代码的编译器时都同意(或必须)遵循的守则(标准)而已。所以说C++语言、Java语言都是一种高级语言标准,学会了一种语言标准,会按照这个标准写程序,再要去学另外一种语言...
  • qq_26849233
  • qq_26849233
  • 2017年05月05日 15:16
  • 379
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:理解NULL如何影响IN和EXITS语句
举报原因:
原因补充:

(最多只允许输入30个字)