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

相关文章

Android实现在线预览office文档的示例详解

《Android实现在线预览office文档的示例详解》在移动端展示在线Office文档(如Word、Excel、PPT)是一项常见需求,这篇文章为大家重点介绍了两种方案的实现方法,希望对大家有一定的... 目录一、项目概述二、相关技术知识三、实现思路3.1 方案一:WebView + Office Onl

Java实现优雅日期处理的方案详解

《Java实现优雅日期处理的方案详解》在我们的日常工作中,需要经常处理各种格式,各种类似的的日期或者时间,下面我们就来看看如何使用java处理这样的日期问题吧,感兴趣的小伙伴可以跟随小编一起学习一下... 目录前言一、日期的坑1.1 日期格式化陷阱1.2 时区转换二、优雅方案的进阶之路2.1 线程安全重构2

Java中的JSONObject详解

《Java中的JSONObject详解》:本文主要介绍Java中的JSONObject详解,需要的朋友可以参考下... Java中的jsONObject详解一、引言在Java开发中,处理JSON数据是一种常见的需求。JSONObject是处理JSON对象的一个非常有用的类,它提供了一系列的API来操作J

HTML5中的Microdata与历史记录管理详解

《HTML5中的Microdata与历史记录管理详解》Microdata作为HTML5新增的一个特性,它允许开发者在HTML文档中添加更多的语义信息,以便于搜索引擎和浏览器更好地理解页面内容,本文将探... 目录html5中的Mijscrodata与历史记录管理背景简介html5中的Microdata使用M

html5的响应式布局的方法示例详解

《html5的响应式布局的方法示例详解》:本文主要介绍了HTML5中使用媒体查询和Flexbox进行响应式布局的方法,简要介绍了CSSGrid布局的基础知识和如何实现自动换行的网格布局,详细内容请阅读本文,希望能对你有所帮助... 一 使用媒体查询响应式布局        使用的参数@media这是常用的

HTML5表格语法格式详解

《HTML5表格语法格式详解》在HTML语法中,表格主要通过table、tr和td3个标签构成,本文通过实例代码讲解HTML5表格语法格式,感兴趣的朋友一起看看吧... 目录一、表格1.表格语法格式2.表格属性 3.例子二、不规则表格1.跨行2.跨列3.例子一、表格在html语法中,表格主要通过< tab

Linux之计划任务和调度命令at/cron详解

《Linux之计划任务和调度命令at/cron详解》:本文主要介绍Linux之计划任务和调度命令at/cron的使用,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录linux计划任务和调度命令at/cron一、计划任务二、命令{at}介绍三、命令语法及功能 :at

Java使用SLF4J记录不同级别日志的示例详解

《Java使用SLF4J记录不同级别日志的示例详解》SLF4J是一个简单的日志门面,它允许在运行时选择不同的日志实现,这篇文章主要为大家详细介绍了如何使用SLF4J记录不同级别日志,感兴趣的可以了解下... 目录一、SLF4J简介二、添加依赖三、配置Logback四、记录不同级别的日志五、总结一、SLF4J

Java使用ANTLR4对Lua脚本语法校验详解

《Java使用ANTLR4对Lua脚本语法校验详解》ANTLR是一个强大的解析器生成器,用于读取、处理、执行或翻译结构化文本或二进制文件,下面就跟随小编一起看看Java如何使用ANTLR4对Lua脚本... 目录什么是ANTLR?第一个例子ANTLR4 的工作流程Lua脚本语法校验准备一个Lua Gramm

Vue3组件中getCurrentInstance()获取App实例,但是返回null的解决方案

《Vue3组件中getCurrentInstance()获取App实例,但是返回null的解决方案》:本文主要介绍Vue3组件中getCurrentInstance()获取App实例,但是返回nu... 目录vue3组件中getCurrentInstajavascriptnce()获取App实例,但是返回n