SQL中NOT IN與NOT EXISTS不等價的問題

更新時間：2024年07月23日 08:59:46 作者：大橙子酸檸檬

在對SQL語句進(jìn)行性能優(yōu)化時,經(jīng)常用到一個技巧是將IN改寫成EXISTS,本文主要介紹了SQL中NOT IN與NOT EXISTS不等價的問題,具有一定的參考價值,感興趣的可以了解一下

一、舉例驗(yàn)證

例如，有如下一張表 rr 。要求：選擇4月2號的數(shù)據(jù)，并且其type1是4月1號沒有的（從表看，就是4月2號C的那條）。

在這里插入圖片描述

使用NOT IN ，單純按照這個條件去實(shí)現(xiàn)

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

在這里插入圖片描述

使用NOT EXISTS

select r1.* from rr as r1
where r1.create_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號的數(shù)據(jù)中，存在type1為NULL的。如果該type1不是NULL，使用NOT IN就可以正確找出來結(jié)果了。

其中的原理涉及三值邏輯。

二、三值邏輯簡述

以下的式子都會被判為unknown
1、 = NULL
2、> NULL
3、< NULL
4、<> NULL
NULL = NULL

unknown，它是因關(guān)系數(shù)據(jù)庫采用了NULL而被引入的“第三個真值”。
（這里還有一點(diǎn)需要注意：真值unknown和作為NULL的一種UNKNOWN（未知）是不同的東西。前者是明確的布爾類型的真值，后者既不是值也不是變量。為了便于區(qū)分，前者采用粗體小寫字母unknown，后者用普通的大寫字母UNKNOWN表示。）

加上true和false，這三個真值之間有下面這樣的優(yōu)先級順序。

AND 的情況：false > unknown > true
OR 的情況：true > unknown > false

下面看具體例子，連同unknown一起理解下

在這里插入圖片描述

三、附錄：用到的SQL

（運(yùn)行環(huán)境Mysql）
1、表 rr 的構(gòu)建

-- 使用了with語句
with rr as (
select '2024-04-01' as create_date,'A' as type1,001 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,'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 'unknown'	
			 end as result3, -- and條件下 的優(yōu)先級展示
			 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條件下 的優(yōu)先級展示
			 case when (not(@b<>@c)) is true then 'true!'
			      when (not(@b<>@c)) is false then 'false!'
			      else 'unknown'	
			 end as result5

到此這篇關(guān)于SQL中NOT IN與NOT EXISTS不等價的問題的文章就介紹到這了,更多相關(guān)SQL NOT IN與NOT EXISTS不等價內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: