前言

今天突然想到之前在書(shū)上看到的一個(gè)例子,竟然想不起來(lái)了.

于是翻書(shū)找出來(lái),測(cè)試一下.

--	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差異開(kāi)始測(cè)試吧，現(xiàn)在測(cè)試使用in、not in 可能帶來(lái)的“錯(cuò)誤”。之所以錯(cuò)誤，是因?yàn)槲覀兛偸且宰匀徽Z(yǔ)言去理解SQL，卻忽略了數(shù)學(xué)中的邏輯語(yǔ)法。不廢話了，測(cè)試看看吧！

【測(cè)試一：in子查詢】

--返回在son中存在的所有father的數(shù)據(jù)
 
--正確的寫(xiě)法：
select * from father where fid in(select fid from son)
 
--錯(cuò)誤的寫(xiě)法：
select * from father where fid in(select oid from son)

說(shuō)明：

兩個(gè)查詢都執(zhí)行沒(méi)有出錯(cuò)，但是第二個(gè)tsql的子查詢寫(xiě)錯(cuò)了。子查詢（select oid from son）實(shí)際單獨(dú)執(zhí)行會(huì)出錯(cuò)，因?yàn)楸韘on不存在字段oid，但是在這里系統(tǒng)不會(huì)提示錯(cuò)誤。而且father表有4行數(shù)據(jù)，所有子查詢掃描了4次son表，但是第二個(gè)查詢中，實(shí)際也只掃描了1次son表，也就是son表沒(méi)有用到。

即使這樣寫(xiě)也 不會(huì)出錯(cuò)：     select*fromfatherwherefidin(selectoid)

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

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

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

【測(cè)試二：not  in子查詢】

--返回在son中不存在的所有father的數(shù)據(jù)
 
--錯(cuò)誤的寫(xiě)法：
select * from father where fid not in(select fid from son)
 
--錯(cuò)誤的寫(xiě)法：
select * from father where fid not in(select oid from son)
 
--正確的寫(xiě)法：
select * from father where fid not in(select fid from son where fid is not null)

說(shuō)明：

查看select fid from son，子查詢中有空值null，子查詢中的值為(2,3,null)，謂詞fid in(2,3,null)永遠(yuǎn)不會(huì)返回false，只反會(huì)true或unknown，所以謂詞fidnot in(2,3,null)只返回not true 或not unknown，結(jié)果都不會(huì)是true。所以當(dāng)子查詢存在null時(shí)，not in和not exists 在邏輯上是不等價(jià)的。

總結(jié)：

In 或 not in在SQL語(yǔ)句中經(jīng)常用到，尤其當(dāng)子查詢中有空值的時(shí)候，要謹(jǐn)慎考慮。因?yàn)榧词箤?xiě)了“正確”的腳本，但是返回結(jié)果卻不正確，也不出錯(cuò)。在不是很理解的情況下，最好使用 exists和 not exists來(lái)替換。而且exists查詢更快一些，因?yàn)橹灰谧硬樵冋业降谝粋€(gè)符合的值就不繼續(xù)往下找了，所以能用exists就用吧。

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

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

 到此這篇關(guān)于T-SQL查詢?yōu)楹紊饔?nbsp;IN和NOT IN詳解的文章就介紹到這了,更多相關(guān)T-SQL查詢慎用 IN和NOT IN內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論