T-SQL查詢為何慎用IN和NOT IN詳解

2022-02-21 13:06:20

前言

今天突然想到之前在書上看到的一個例子,竟然想不起來了.

於是翻書找出來,測試一下.

--	drop table father,son
create table father(fid int,name varchar(10),oid int)
create table son(sid int,name varchar(10),fid int)
 
 
insert into father(fid,name,oid)
values(1,'father',5),(2,'father',9),(3,'father',null),(4,'father',0)
 
insert into son(sid,name,fid)
values(1,'son',2),(2,'son',2),(3,'son',3),(4,'son',null),(5,'son',null)
 
select * from father
select * from son

in和exists差異開始測試吧，現在測試使用in、not in 可能帶來的“錯誤”。之所以錯誤，是因為我們總是以自然語言去理解SQL，卻忽略了數學中的邏輯語法。不廢話了，測試看看吧！

【測試一：in子查詢】

--返回在son中存在的所有father的資料
 
--正確的寫法：
select * from father where fid in(select fid from son)
 
--錯誤的寫法：
select * from father where fid in(select oid from son)

說明：

兩個查詢都執行沒有出錯，但是第二個tsql的子查詢寫錯了。子查詢（select oid from son）實際單獨執行會出錯，因為表son不存在欄位oid，但是在這裡系統不會提示錯誤。而且father表有4行資料，所有子查詢掃描了4次son表，但是第二個查詢中，實際也只掃描了1次son表，也就是son表沒有用到。

即使這樣寫也不會出錯： select*fromfatherwherefidin(selectoid)

這個查詢的意思是，表father中每行的fid與oid比較，相同則返回值。

實際查詢是這樣的： select * from father where fid = oid

測試一中，fid in(select fid from son)子查詢中包含null值，所以 fid in（null）返回的是一個未知值。但是在刷選器中，false和unknown的處理方式類似。因此第一個子查詢返回了正確的結果集。

【測試二：not in子查詢】

--返回在son中不存在的所有father的資料
 
--錯誤的寫法：
select * from father where fid not in(select fid from son)
 
--錯誤的寫法：
select * from father where fid not in(select oid from son)
 
--正確的寫法：
select * from father where fid not in(select fid from son where fid is not null)

說明：

檢視select fid from son，子查詢中有空值null，子查詢中的值為(2,3,null)，謂詞fid in(2,3,null)永遠不會返回false，只反會true或unknown，所以謂詞fidnot in(2,3,null)只返回not true 或not unknown，結果都不會是true。所以當子查詢存在null時，not in和not exists 在邏輯上是不等價的。

總結：

In 或 not in在SQL語句中經常用到，尤其當子查詢中有空值的時候，要謹慎考慮。因為即使寫了“正確”的指令碼，但是返回結果卻不正確，也不出錯。在不是很理解的情況下，最好使用 exists和 not exists來替換。而且exists查詢更快一些，因為只要在子查詢找到第一個符合的值就不繼續往下找了，所以能用exists就用吧。

select *fromfatherawhereexists(select 1fromsonbwherea.fid=b.fid)

select * from father awherenotexists(select 1fromsonbwherea.fid=b.fid)

到此這篇關於T-SQL查詢為何慎用 IN和NOT IN詳解的文章就介紹到這了,更多相關T-SQL查詢慎用 IN和NOT IN內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

T-SQL查詢為何慎用IN和NOT IN詳解

目錄

前言

【測試一：in子查詢】

【測試二：not in子查詢】

總結：

熱門文章