今天忽然想到之前在書上看到的一個(gè)比如,竟然想不起來(lái)了.

所以翻書找出來(lái),測(cè)驗(yàn)一下.

-- 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差異開端測(cè)驗(yàn)吧，現(xiàn)在測(cè)驗(yàn)運(yùn)用in、not in 或許帶來(lái)的“過(guò)錯(cuò)”。之所以過(guò)錯(cuò)，是由于咱們總是以自然語(yǔ)言去了解SQL，卻忽略了數(shù)學(xué)中的邏輯語(yǔ)法。不廢話了，測(cè)驗(yàn)看看吧！

【測(cè)驗(yàn)一：in子查詢】

--回來(lái)在son中存在的一切father的數(shù)據(jù)

--正確的寫法：
select * from father where fid in(select fid from son)

--過(guò)錯(cuò)的寫法：
select * from father where fid in(select oid from son)

闡明：

兩個(gè)查詢都履行沒有犯錯(cuò)，可是第二個(gè)tsql的子查詢寫錯(cuò)了。子查詢（select oid from son）實(shí)踐單獨(dú)履行會(huì)犯錯(cuò)，由于表son不存在字段oid，可是在這里體系不會(huì)提示過(guò)錯(cuò)。而且father表有4行數(shù)據(jù)，一切子查詢掃描了4次son表，可是第二個(gè)查詢中，實(shí)踐也只掃描了1次son表，也便是son表沒有用到。

即便這樣寫也 不會(huì)犯錯(cuò)：select*fromfatherwherefidin(selectoid)

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

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

測(cè)驗(yàn)一中，fid in(select fid from son)子查詢中包含null值，所以 fid  in（null）回來(lái)的是一個(gè)未知值?？墒窃谒⑦x器中，false和unknown的處理方式類似。因而榜首個(gè)子查詢回來(lái)了正確的成果集。

【測(cè)驗(yàn)二：not  in子查詢】

--回來(lái)在son中不存在的一切father的數(shù)據(jù)

--過(guò)錯(cuò)的寫法：
select * from father where fid not in(select fid from son)

--過(guò)錯(cuò)的寫法：
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)永遠(yuǎn)不會(huì)回來(lái)false，只反會(huì)true或unknown，所以謂詞fidnot in(2,3,null)只回來(lái)not true 或not unknown，成果都不會(huì)是true。所以當(dāng)子查詢存在null時(shí)，not in和not exists 在邏輯上是不等價(jià)的。

總結(jié)

In 或 not in在SQL語(yǔ)句中經(jīng)常用到，尤其當(dāng)子查詢中有空值的時(shí)候，要慎重考慮。由于即便寫了“正確”的腳本，可是回來(lái)成果卻不正確，也不犯錯(cuò)。

在不是很了解的情況下，最好運(yùn)用 exists和 not exists來(lái)替換。而且exists查詢更快一些，由于只要在子查詢找到榜首個(gè)契合的值就不繼續(xù)往下找了，所以能用exists就用吧。