从表面上看,IN和EXITS的SQL语句是可互换和等效的。然而,它们在处理UULL数据时会有很大的差别,并导致不同的结果。问题的根源是在一 个Oracle数据库中,一个NULL值意味着未知变量,所以操作NULL值的比较函数的结果也是一个未知变量,而且任何返回NULL的值通常也被忽略。 例如,以下查询都不会返回一行的值:
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出现在任一子查询中时,你可以避免一些常见的问题。
原文地址:http://www.kuqin.com/article/17mysql/2005041812275611291.html
相关推荐
User Exits in SAP BW
exits完全退出
一个非常强的查找Customer Exit, User exit, BADI和BTE的程序 博文链接:https://jgtang82.iteye.com/blog/128474
一些常用和比较便的sql语句使用 和效率分析
Tor退出安装 npm install tor-exits用法 var tor = require ( 'tor-exits' ) ;tor . fetch ( function ( err , data ) { if ( err ) return console . error ( err ) ; var nodes = tor . parse ( data ) ; console ...
2019 ICM PROBLEM D: Emergency exits. (2019 美赛 D 题: 用元胞自动机模拟逃生出口问题)
SAP PM模块的用户出口总揽及介绍 ABAP语言使用
IBM 大型计算机平台下进行EXIT安装教程,英文原版。
摘要:Delphi源码,数据库...Delphi使用exists和noexists进行数据库的查询实例。可以求交集和差集,有兴趣参考下。带有数据库文件,在Database文件夹下,测试前请附加好数据库并连接数据库信息。 运行环境:Delphi+MSSQL
db.execSQL("drop table datas if exits"); onCreate(db); } public Cursor ExitsData() { SQLiteDatabase db = getReadableDatabase(); return db.rawQuery("select * from datas",null); } ...
Determining the required stack sizes for a software project is a crucial part of the development process. The developer aims to create a ... when the function exits, it removes that data from the stack.
本代码为文章:WINCC读写SQL数据库的例子【附wincc项目文件】的实例文件。
Printed in full color—figures and code appear as they do in Xcode 7.x • Learn to navigate the Xcode 7.x development environment and install apps on your iDevice • Get started quickly with Apple’s ...
使用的方法是:首先定义(T-Code:CMOD)一个项目Project(以管理你的增强,这里的项目和PS模块的项目可是两回事),把你要使用的系统增加Enhancement分配给这个项目,编辑系统增强中的用户出口对象。
programs and manuals, read this file in its entirety. TABLE OF CONTENTS ----------------- 1. How to Get Help 2. Installation 3. Features 4. Important Information 5. Testing Your Expanded Memory 6. ...
programs and manuals, read this file in its entirety. TABLE OF CONTENTS ----------------- 1. How to Get Help 2. Installation 3. Features 4. Important Information 5. Testing Your Expanded Memory 6. ...
New (Bug 3184): Properties inspector now saves changes automatically when application exits. Fixed (Bug 4264): UML 2.0, Sequence diagram: Lifeline head changes its shape depending on the metaclass of...
delete from a where no in (select no from b); 14、查询从多少行到多少行的记录(可以用在web开发中的分页显示) select * from ( select rownum row_id,b.* from (select a.* from sys_oper a) b ) where row_...
10代笔记本图纸 INTEL 10nm ICL-U RDK 4. * SUFFIX INDICATES ACTIVE LOW SIGNAL. 3. VCC = +5V UNLESS OTHERWISE SPECIFIED. NOTES: ...5. \I SUFFIX INDICATES SIGNAL EXITS HIERARCHICAL BLOCK.