http://x-spirit.iteye.com/blog/615603
首先我要感谢aa和Liu Xing帮我发现了我日志中的错误。之前比较粗心,把3条SQL语句写成一样的了,对于给读者造成的麻烦,我深表抱歉。
今天我把原文做了修订,为了对得起读者对我的关注,我重新深入的研究了这个问题,在后面,我会把来龙去脉写清楚。
问题:
语句1 :
Select * from table1 A where A.col1 not in ( select col1 from table2 B )
转载注明出处:http://x- spirit.iteye.com/、http: //www.blogjava.net/zhangwei217245/
如果这样,本来应该有一条数据,结果没有。
如果我改写成这样:
语句2 :
select * from table1 A where not exists ( SELECT * FROM table2 B where B.col1 = A.col1)
结果就正确,有一条数据显示。
转载注明出处:http://x- spirit.iteye.com/、http: //www.blogjava.net/zhangwei217245/
经过一番搜索,原以为是子查询结果集太大的原因。
后来有网上强人指点:子查询里面有空集。即子查询的结果集里面有NULL的结果。
把查询语句修改成:
语句3 :
Select * from table1 A where A.col1 not in ( select col1 from table2 B where B.col1 is not null )
果然就查出来了。而且一点不差。。。厉害阿~~~
下面是针对本文题的分析:
1。 首先来说说Oracle中的NULL。
Oracle中的NULL代表的是无意义,或者没有值。将NULL和其他的值进行逻辑运算,运算过程中,NULL的表现更象是FALSE。
下面请看真值表:
AND NULL
OR NULL
TRUE NULL TRUE
FALSE FALSE NULL
NULL NULL
NULL
另外,NULL和其他的值进行比较或者算术运算(<、>、=、!=、+、-、*、/),结果仍是NULL。
如果想要判定某个值是否为NULL,可以用IS NULL或者IS NOT NULL。
2. 再来说说Oracle中的IN。
in是一个成员条件, 对于给定的一个集合或者子查询,它会比较每一个成员值。
IN功能上相当于 =ANY 的操作,而NOT IN 功能上相当于 !=ALL 的操作。
IN在逻辑上实际上就是对给定的成员集合或者子查询结果集进行逐条的判定,例如:
SELECT * FROM table1 A WHERE A.col1 in ( 20 , 50 , NULL );
实际上就是执行了
SELECT * FROM table1 A WHERE A.col1 = 20 OR A.col1 = 50 OR A.col1 = NULL ;
这样,根据NULL的运算特点和真值表,我们可以看出,上边这个WHERE 字句可以被简化(如果返回NULL则无结果集返回,这一点和FALSE是一样的)为
WHERE A.col1 = 20 OR A.col1 = 50
也就是说,如果你的table1中真的存在含有NULL值的col1列,则执行该语句,无法查询出那些值为null的记录。
再来看看NOT IN。根据逻辑运算关系,我们知道,NOT (X=Y OR N=M) 等价于 X!=Y AND N!=M,那么:
SELECT * FROM table1 A WHERE A.col1 not in ( 20 , 50 , NULL )
等价于
SELECT * FROM table1 A WHERE A.col1 != 20 AND A.col1 != 50 AND A.col1 != NULL
根据NULL的运算特性和真值表,该语句无论前两个判定条件是否为真,其结果一定是NULL或者FALSE。故绝对没有任何记录可以返回。
这就是为什么语句1 查不到应有结果的原因。当然,如果你用NOT IN的时候,预先在子查询里把NULL去掉的话,那就没问题了,例如语句3 。
有些童鞋可能要问了:那如果我想把A表里面那些和B表一样col1列的值一样的记录都查出来,即便A、B两表里面的col1列都包括值为NULL的记录的 话,用这一条语句就没办法了吗?
我只能很遗憾的告诉你,如果你想在WHERE后面单纯用IN 似乎不太可能了,当然,你可以在外部的查询语句中将NULL条件并列进去,例如:
SELECT * FROM table1 A WHERE A.col1 in ( SELECT B.col1 FROM table2 B) OR A.col1 IS NULL ;
转载注明出处:http://x- spirit.iteye.com/、http: //www.blogjava.net/zhangwei217245/
3. 最后谈谈EXISTS。
有人说EXISTS的性能比IN要好。但这是很片面的。我们来看看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 RECORD in x
end if
end loop
转载注明出处:http://x- spirit.iteye.com/、http: //www.blogjava.net/zhangwei217245/
也就是说,EXISTS语句实际上是通过循环外部查询的结果集,来过滤出符合子查询标准的结果集。于是外部查询的结果集数量对该语句执行性能影响最大,故 如果外部查询的结果集数量庞大,用EXISTS语句的性能也不一定就会好很多。
转载注明出处:http://x- spirit.iteye.com/、http: //www.blogjava.net/zhangwei217245/
当然,有人说NOT IN是对外部查询和子查询都做了全表扫描,如果有索引的话,还用不上索引,但是NOT EXISTS是做连接查询,所以,如果连接查询的两列都做了索引,性能会有一定的提升。
当然至于实际的查询效率,我想还是具体情况具体分析吧。
那么我们不妨来分析一下语句2为什么能够的到正确的结果吧:
语句2是这样的:
select * from table1 A where not exists ( SELECT B.col1 FROM table2 B where B.col1 = A.col1)
实际上是这样的执行过程:
for x in ( select * from table1 A )
loop
if (not exists ( select * from table2 B where B.col1 = x.col1 )
then
OUTPUT THE RECORD in x
end if
end loop
转载注明出处:http://x- spirit.iteye.com/、http: //www.blogjava.net/zhangwei217245/
由于表A中不包含NULL的记录,所以,遍历完表A,也只能挑出表A中独有的记录。
这就是为什么语句2 能够完成语句3 的任务的原因。
但如果表A中存在NULL记录而表B中不存在呢?
这个问题请大家自己分析吧。哈哈。有答案了可以给我留言哦。
答案:A表中的NULL也会被查出来。因为select * from table2 B where B.col1 = NULL不返回结果,故
not exists ( select * from table2 B where B.col1 = x.col1 )的值为真。
转载注明出处:http://x- spirit.iteye.com/、http: //www.blogjava.net/zhangwei217245/
以上SQL运行结果在MySQL和Oracle上都已经通过。
分享到:
相关推荐
NOT EXISTS ⇔ NOT IN ⇒ NOT NULL 问题 结果不一样 疑问 如果等效
oracle中exists_和in的效率问题详解
NOT IN、JOIN、IS NULL、NOT EXISTS效率对比 语句一:select count(*) from A where A.a not in (select a from B) 语句二:select count(*) from A left join B on A.a = B.a where B.a is null 语句三:select ...
关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in...
总结:exists 和not exists语句强调是否返回结果集,不要求知道返回什么,与in的区别就是,in只能返回一个字段值,exists允许返回多个字段 提醒:文章中提供了exists和not exists的常用示例,已经经过本人测试,...
里面自己根据网上的资源整理出来的一份sql中in,exists,not in,not exists的使用方法以及注意事项等,有助于初学的朋友们借鉴。
“exists”和“in”是Oracle中,都是查询某集合的值是否存在在另一个集合,但对不同的数据有不同的用法,主要是在效率问题上存在很大的差别,以下有两个简单例子,以说明 “exists”和“in”的效率问题。
一个是问in exist的区别,一个是not in和not exists的区别
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt欢迎下载!
oracle in和exists性能解析
MySQL优化之in,exists,not in,not exists的区别in与existsin查询过程结论:exists查询过程:结论:not in与not existsnot in查询过程:结论:not exists查询过程:结论: 首先我们使用两个用户表作为实例 insert ...
NULL 博文链接:https://576017120.iteye.com/blog/1624774
oracle数据库关于exists使用方法与in的比较
一直以来,大家认为exists比in速度快,其实是不准确的。且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询。 如果两张表大小差不多,那么...
function_exexists函数详解function_exists函数详解function_exists函数详解function_exists函数详解function_existexists函数详解function_exists函数详解function_exists函数详解function_exists函数详解function_...
为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。 如 我要查询 Sendorder表中的冗余数据(没有和reg_person或worksite相连的数据) sql=”select Sendorder.id,Sendorder.reads,Send...
主要给大家介绍了关于oracle中not exists对外层查询的影响,文中介绍的非常详细,需要的朋友可以参考下。