<strong id="hvixb"></strong>

<center id="hvixb"><strong id="hvixb"></strong></center>

<fieldset id="hvixb"></fieldset>

<samp id="hvixb"><strong id="hvixb"></strong></samp>

sql中notin與notexists的區(qū)別有哪些

這篇文章主要為大家展示了“sql中not in與not exists的區(qū)別有哪些”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“sql中not in與not exists的區(qū)別有哪些”這篇文章吧。

創(chuàng)新互聯(lián)公司是一家專業(yè)提供徐水企業(yè)網(wǎng)站建設(shè),專注與成都做網(wǎng)站、成都網(wǎng)站制作、H5建站、小程序制作等業(yè)務(wù)。10年已為徐水眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站建設(shè)公司優(yōu)惠進(jìn)行中。

我先建兩個(gè)示范表，便于說(shuō)明：

create table ljn_test1 (col number);

create table ljn_test2 (col number);

然后插入一些數(shù)據(jù)：

insert into ljn_test1

select level from dual connect by level <=30000;

insert into ljn_test2

select level+1 from dual connect by level <=30000;

commit;

然后來(lái)分別看一下使用not exists和not in的性能差異：

select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);

COL

----------

1

Elapsed: 00:00:00.06

select * from ljn_test1 where col not in (select col from ljn_test2);

COL

----------

1

Elapsed: 00:00:21.28

可以看到，使用not exists需要0.06秒，而使用not in需要21秒，差了3個(gè)數(shù)量級(jí)！為什么呢？其實(shí)答案很簡(jiǎn)答，以上兩個(gè)SQL其實(shí)并不是等價(jià)的。

我把以上兩個(gè)表的數(shù)據(jù)清除掉，重新插入數(shù)據(jù)：

truncate table ljn_test1;

truncate table ljn_test2;

insert into ljn_test1 values(1);

insert into ljn_test1 values(2);

insert into ljn_test1 values(3);

insert into ljn_test2 values(2);

insert into ljn_test2 values(null);

commit;

然后再次執(zhí)行兩個(gè)SQL：

select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);

COL

----------

3

1

select * from ljn_test1 where col not in (select col from ljn_test2);

no rows selected

這回not in的原形暴露了，竟然得到的是空集。來(lái)仔細(xì)分解一下原因：

A. select * from ljn_test1 where col not in (select col from ljn_test2);

A在這個(gè)例子中可以轉(zhuǎn)化為下面的B：

B. select * from ljn_test1 where col not in (2,null);

B可以進(jìn)一步轉(zhuǎn)化為下面的C：

C. select * from ljn_test1 where col <> 2 and col <> null;

因?yàn)閏ol <> null是一個(gè)永假式，所以最終查出的結(jié)果肯定也就是空了。

由此可以得出結(jié)論：只要not in的子查詢中包含空值，那么最終的結(jié)果就為空！

not exists語(yǔ)句不會(huì)出現(xiàn)這種情況，因?yàn)閚ot exists子句中寫的是ljn_test1與ljn_test2的關(guān)聯(lián)，null是不參與等值關(guān)聯(lián)的，所以ljn_test2的col存在空值對(duì)最終的查詢結(jié)果沒(méi)有任何影響。

我在這里暫且把ljn_test1叫做外表，ljn_test2叫做內(nèi)表。

只要稍做歸納，就可以得到更詳細(xì)的結(jié)論：

1、對(duì)于not exists查詢，內(nèi)表存在空值對(duì)查詢結(jié)果沒(méi)有影響；對(duì)于not in查詢，內(nèi)表存在空值將導(dǎo)致最終的查詢結(jié)果為空。

2、對(duì)于not exists查詢，外表存在空值，存在空值的那條記錄最終會(huì)輸出；對(duì)于not in查詢，外表存在空值，存在空值的那條記錄最終將被過(guò)濾，其他數(shù)據(jù)不受影響。

講到這里，我就可以開(kāi)始解釋為什么上面的not in語(yǔ)句比not exists語(yǔ)句效率差這么多了。

not exists語(yǔ)句很顯然就是一個(gè)簡(jiǎn)單的兩表關(guān)聯(lián)，內(nèi)表與外表中存在空值本身就不參與關(guān)聯(lián)，在CBO(基于成本的優(yōu)化器)中常用的執(zhí)行計(jì)劃是hash join，所以它的效率完全沒(méi)有問(wèn)題，看一下它的執(zhí)行計(jì)劃：

set autot on;

select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);

COL

----------

3

1

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 385135874

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 78 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN ANTI | | 3 | 78 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| LJN_TEST1 | 3 | 39 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| LJN_TEST2 | 2 | 26 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("LJN_TEST1"."COL"="LJN_TEST2"."COL")

這個(gè)執(zhí)行計(jì)劃很清晰，沒(méi)有什么需要解釋的，再看一下not in:

select * from ljn_test1 where col not in (select col from ljn_test2);

no rows selected

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 3267714838

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL| LJN_TEST1 | 3 | 39 | 3 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| LJN_TEST2 | 2 | 26 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "LJN_TEST2" "LJN_TEST2"

WHERE LNNVL("COL"<>:B1)))

3 - filter(LNNVL("COL"<>:B1))

可以看到關(guān)聯(lián)謂詞是filter，它類似于兩表關(guān)聯(lián)中的nested loop，也就是跑兩層循環(huán)，可見(jiàn)它的效率有多差。為什么not in不能使用hash join作為執(zhí)行計(jì)劃呢？正如上面解釋的，因?yàn)閮?nèi)表或外表中存在空值對(duì)最終結(jié)果產(chǎn)生的影響是hash join無(wú)法實(shí)現(xiàn)的，因?yàn)閔ash join不支持把空值放到hash桶中，所以它沒(méi)辦法處理外表和內(nèi)表中存在的空值，效率與正確性放在一起時(shí)，肯定是要選擇正確性，所以oracle必須放棄效率，保證正確性，采用filter謂詞。

這個(gè)執(zhí)行計(jì)劃中我們還有感興趣的東西，那就是：LNNVL("COL"<>:B1)，關(guān)于LNNVL的解釋可以參見(jiàn)官方文檔：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions078.htm

它在這里的作用很巧妙，oracle知道使用filter性能很差，所以它在掃描內(nèi)表ljn_test2時(shí)，會(huì)使用LNNVL來(lái)檢查ljn_test2.col是否存在null值，只要掃描到null值，就可以斷定最終的結(jié)果為空值，也就沒(méi)有了繼續(xù)執(zhí)行的意義，所以oracle可以馬上終止執(zhí)行，在某種意義上它彌補(bǔ)了filter較差的性能。

我用例子來(lái)證明這一點(diǎn)，首先先造一些數(shù)據(jù)：

truncate table ljn_test1;

truncate table ljn_test2;

insert into ljn_test1

select level from dual connect by level <=30000;

insert into ljn_test2

select level+1 from dual connect by level <=30000;

commit;

然后我為了讓oracle盡快掃描到ljn_test2.col為null的那條記錄，我要先找到物理地址最小的那條記錄，因?yàn)橥ǔＧ闆r全表掃描會(huì)先掃描物理地址最小的那條記錄：

select col from ljn_test2 where rowid=(select min(rowid) from ljn_test2);

COL

----------

1982

然后我把這條記錄更新為空：

update ljn_test2 set col = null where col=1982;

commit;

然后再來(lái)看一下not in的查詢效率：

select * from ljn_test1 where col not in (select col from ljn_test2);

no rows selected

Elapsed: 00:00:00.17

看到這個(gè)結(jié)果后我很爽，它和之前查詢需要用時(shí)21秒有很大的差別！

當(dāng)然，我們不能總是指望oracle掃描表時(shí)總是最先找到null值，看下面的例子：

update ljn_test2 set col = 1982 where col is null;

select col from ljn_test2 where rowid=(select max(rowid) from ljn_test2);

COL

----------

30001

update ljn_test2 set col = null where col=30001;

commit;

再看一下not in的查詢效率：

select * from ljn_test1 where col not in (select col from ljn_test2);

COL

----------

1

Elapsed: 00:00:21.11

這一下not in再一次原形畢露了！

機(jī)會(huì)主義不行，更杯具的是如果內(nèi)表中沒(méi)有空值，那LNNVL優(yōu)化就永遠(yuǎn)起不到作用，相反它還會(huì)增大開(kāi)銷！

其實(shí)只要找到原因，問(wèn)題很好解決，不就是空值在作怪嘛！在正常的邏輯下用戶本來(lái)就是想得到和not exists等價(jià)的查詢結(jié)果，所以只要讓oracle知道我們不需要空值參與進(jìn)來(lái)就可以了。

第一種解決方案：

將內(nèi)表與外表的關(guān)聯(lián)字段設(shè)定為非空的：

alter table ljn_test1 modify col not null;

alter table ljn_test2 modify col not null;

好了，再看一下執(zhí)行計(jì)劃：

set autot on;

select * from ljn_test1 where col not in (select col from ljn_test2);

COL

----------

1

Elapsed: 00:00:00.07

Execution Plan

----------------------------------------------------------

Plan hash value: 385135874

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 26 | 28 (8)| 00:00:01 |

|* 1 | HASH JOIN ANTI | | 1 | 26 | 28 (8)| 00:00:01 |

| 2 | TABLE ACCESS FULL| LJN_TEST1 | 30000 | 380K| 13 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| LJN_TEST2 | 30000 | 380K| 13 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("COL"="COL")

很好！這回oracle已經(jīng)知道使用hash join了！不過(guò)有時(shí)候表中需要存儲(chǔ)空值，這時(shí)候就不能在表結(jié)構(gòu)上指定非空了，那也同樣簡(jiǎn)單：

第二種解決方案：

查詢時(shí)在內(nèi)表與外表中過(guò)濾空值。

先把表結(jié)構(gòu)恢復(fù)為允許空值的：

alter table ljn_test1 modify col null;

alter table ljn_test2 modify col null;

然后改造查詢：

select * from ljn_test1 where col is not null and col not in (select col from ljn_test2 where col is not null);

COL

----------

1

Elapsed: 00:00:00.07

Execution Plan

----------------------------------------------------------

Plan hash value: 385135874

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 26 | 28 (8)| 00:00:01 |

|* 1 | HASH JOIN ANTI | | 1 | 26 | 28 (8)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| LJN_TEST1 | 30000 | 380K| 13 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| LJN_TEST2 | 30000 | 380K| 13 (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("COL"="COL")

2 - filter("COL" IS NOT NULL)

3 - filter("COL" IS NOT NULL)

OK! hash join出來(lái)了！我想我關(guān)于not exists與not in之間的比較也該結(jié)束了。

以上是“sql中not in與not exists的區(qū)別有哪些”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

分享名稱：sql中notin與notexists的區(qū)別有哪些
文章出自：http://bm7419.com/article24/pcioce.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供靜態(tài)網(wǎng)站、網(wǎng)站收錄、定制開(kāi)發(fā)、響應(yīng)式網(wǎng)站、網(wǎng)站設(shè)計(jì)、微信公眾號(hào)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

綿陽(yáng)服務(wù)器托管

解決方案知識(shí)

行業(yè)網(wǎng)站建設(shè)

<bdo id="kumwq"></bdo>

<ol id="kumwq"></ol>

<bdo id="kumwq"></bdo>

<center id="kumwq"><listing id="kumwq"></listing></center>

<mark id="kumwq"><listing id="kumwq"></listing></mark>

<ol id="kumwq"></ol>

<ol id="kumwq"></ol>