SQL中NOT IN与NOT EXISTS不等价的问题

目录

• 一、举例验证
• 二、三值逻辑简述
• 三、附录：用到的SQL

在对SQL语句进行性能优化时，经常用到一个技巧是将IN改写成EXISTS，这是等价改写，并没有什么问题。问题在于，将NOT IN改写成NOT EXISTS时，结果未必一样。

执行环境：mysql

一、举例验证

例如，有如下一张表 rr 。要求：选择4月2号的数据，并且其type1是4月1号没有的（从表看，就是4月2号C的那条）。

使用NOT IN ，单纯按照这个条件去实现

select * from rr 
where create_date='2024-04-02'
 and type1 not in (
		select type1 from rr 
		where create_dajste='2024-04-01'
	)
;

使用NOT EXISTS

select r1.* from rr as r1
where r1.cr编程客栈eate_date='2024-04-02'
 and not exists (
		select r2.type1 from rr as r2 
		where r2.create_date='2024-04-01' and r1.type1=r2.type1
	)
;

主要原因是4月1号的数据中，存在type1为NULL的。如果该type1不是NULL，使用NOT IN就可以正确找出来结果了。

其中的原理涉及三值逻辑。

二、三值逻辑简述

以下的式子都会被判为unknown

1、 = NULL

2、> NULL

3、< NULL

4、<> NULL

NULL = NULL

unknown，它是因关系数据库采用了NULL而被引入的“第三个真值”。

（这里还有一点需要注意：真值unknown和作为NULL的一种UNKNOWN（未知）是不同的东西。前者是明确的布尔类型的真值，后者既不是值也不是变量。为了便于区分，前者采用粗体小写字母unknown，后者用普通的大写字母UNKNOWN表示。）

加上true和false，这三个真值之间有下面这样的优先级顺序。

• AND 的情况：false > unknown > true
• OR 的情况：true > unknown > false

下面看具体例子，连同unknown一起理解下

三、附录：用到的SQL

（运行环境Mysql）

1、表 rr 的构编程客栈建

-- 使用了with语句
with rr as (
select '2024-04-01' as create_date,'A' as type1,001 as codwww.devze.come1
 union all select '2024-04-01' as create_date,'A' as type1,002 as code1
 union all select '2024-04-01' as create_date,'A' as type1,002 as code1
 union all select '2024-04-01' as create_date,'B' as type1,013 as code1
 union all select '2024-04-01' as create_date,null as type1,013 as code1
 union all select '2024-04-02' as create_date,'B' as type1,013 as code1
 union all select '2024-04-02' as create_date,'C' as type1,109 as code1
 union all select '2024-04-03' as create_date,'A' as type1,002 as code1
 union all select '2024-04-04' as create_date,'A' as type1,002 as code1
)

2、 unknown的理解

set @a:=2, @b:=5, @c:= NULL ;

select @a+@b as result1,
       case when (@b>@c) is true then 'true!'
			      when (@b>@c) is false then 'false!'
			      else 'unknown'	
			 end as result2, -- 与NULL比较		 
       case when (@a<@b and @b>@c) is true then 'true!'
			      when (@a<@b and @b>@c) is false then 'false!'
			      else 'unknopythonwn'	
			 end as result3, -- and条件下 的优先级展示
			 case when (@a<@b or @b>@c) is true then 'true!'
			      when (@a<@b or @b>@c) is false then 'false!'
			      else 'unknown'	
			 end as result4, -- or条件下 的优先级展示
			 case when (not(@b<>@c)) is true then 'true!'
			      when (not(@b<>@c)) is false then 'false!'
			      else 'unknown'	
			 end as result5

到此这篇关于SQL中NOT IN与NOT EXISTS不等价的问题的文章就介绍到这了,更多相关SQL NOT IN与NOT EXISTS不等价内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！