Oracle not in查不到应有的结果(NULL、IN、EXISTS详解)

2023-12-11 13:33

本文主要是介绍Oracle not in查不到应有的结果(NULL、IN、EXISTS详解),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

语句1:
Select * from  table1 A where A.col1 not in ( select col1 from table2 B )

如果这样,本来应该有一条数据,结果没有。
如果我改写成这样:

语句2:
select * from table1 A where not exists (SELECT * FROM table2 B where B.col1 = A.col1)结果就正确,有一条数据显示。


经过一番搜索,原以为是子查询结果集太大的原因。

后来有网上强人指点:子查询里面有空集。即子查询的结果集里面有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;


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
也就是说,EXISTS语句实际上是通过循环外部查询的结果集,来过滤出符合子查询标准的结果集。于是外部查询的结果集数量对该语句执行性能影响最大,故如果外部查询的结果集数量庞大,用EXISTS语句的性能也不一定就会好很多。

当然,有人说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
由于表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 )的值为真。

 

以上SQL运行结果在MySQL和Oracle上都已经通过。

 

原文:http://www.blogjava.net/zhangwei217245/archive/2010/03/01/310708.html,感谢。

这篇关于Oracle not in查不到应有的结果(NULL、IN、EXISTS详解)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/480860

相关文章

Linux换行符的使用方法详解

《Linux换行符的使用方法详解》本文介绍了Linux中常用的换行符LF及其在文件中的表示,展示了如何使用sed命令替换换行符,并列举了与换行符处理相关的Linux命令,通过代码讲解的非常详细,需要的... 目录简介检测文件中的换行符使用 cat -A 查看换行符使用 od -c 检查字符换行符格式转换将

详解C#如何提取PDF文档中的图片

《详解C#如何提取PDF文档中的图片》提取图片可以将这些图像资源进行单独保存,方便后续在不同的项目中使用,下面我们就来看看如何使用C#通过代码从PDF文档中提取图片吧... 当 PDF 文件中包含有价值的图片,如艺术画作、设计素材、报告图表等,提取图片可以将这些图像资源进行单独保存,方便后续在不同的项目中使

Android中Dialog的使用详解

《Android中Dialog的使用详解》Dialog(对话框)是Android中常用的UI组件,用于临时显示重要信息或获取用户输入,本文给大家介绍Android中Dialog的使用,感兴趣的朋友一起... 目录android中Dialog的使用详解1. 基本Dialog类型1.1 AlertDialog(

C#数据结构之字符串(string)详解

《C#数据结构之字符串(string)详解》:本文主要介绍C#数据结构之字符串(string),具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录转义字符序列字符串的创建字符串的声明null字符串与空字符串重复单字符字符串的构造字符串的属性和常用方法属性常用方法总结摘

Java中StopWatch的使用示例详解

《Java中StopWatch的使用示例详解》stopWatch是org.springframework.util包下的一个工具类,使用它可直观的输出代码执行耗时,以及执行时间百分比,这篇文章主要介绍... 目录stopWatch 是org.springframework.util 包下的一个工具类,使用它

Java进行文件格式校验的方案详解

《Java进行文件格式校验的方案详解》这篇文章主要为大家详细介绍了Java中进行文件格式校验的相关方案,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录一、背景异常现象原因排查用户的无心之过二、解决方案Magandroidic Number判断主流检测库对比Tika的使用区分zip

Java实现时间与字符串互相转换详解

《Java实现时间与字符串互相转换详解》这篇文章主要为大家详细介绍了Java中实现时间与字符串互相转换的相关方法,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录一、日期格式化为字符串(一)使用预定义格式(二)自定义格式二、字符串解析为日期(一)解析ISO格式字符串(二)解析自定义

springboot security快速使用示例详解

《springbootsecurity快速使用示例详解》:本文主要介绍springbootsecurity快速使用示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝... 目录创www.chinasem.cn建spring boot项目生成脚手架配置依赖接口示例代码项目结构启用s

Python中随机休眠技术原理与应用详解

《Python中随机休眠技术原理与应用详解》在编程中,让程序暂停执行特定时间是常见需求,当需要引入不确定性时,随机休眠就成为关键技巧,下面我们就来看看Python中随机休眠技术的具体实现与应用吧... 目录引言一、实现原理与基础方法1.1 核心函数解析1.2 基础实现模板1.3 整数版实现二、典型应用场景2

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML