前言

相信對(duì)于每位程序員來說，sql查詢這個(gè)東西， 要說它簡(jiǎn)單， 可以很簡(jiǎn)單， 通常情況下只需使用增刪查改配合編程語言的邏輯表達(dá)能力，就能實(shí)現(xiàn)所有功能。 但是增刪查改并不能代表sql語句的所有， 完整的sql功能會(huì)另人望而生畏。 就拿比普通增刪查改稍微復(fù)雜一個(gè)層次的連接查詢來說, 盲目使用, 也會(huì)出現(xiàn)意料之外的危險(xiǎn)結(jié)果，導(dǎo)致程序出現(xiàn)莫名其妙的BUG。

在連接查詢語法中，另人迷惑首當(dāng)其沖的就要屬on篩選和where篩選的區(qū)別了， 在我們編寫查詢的時(shí)候， 篩選條件的放置不管是在on后面還是where后面， 查出來的結(jié)果總是一樣的， 既然如此，那為什么還要多此一舉的讓sql查詢支持兩種篩選器呢？ 事實(shí)上， 這兩種篩選器是存在差別的，只是如果不深挖不容易發(fā)現(xiàn)而已。

sql中的連接查詢分為3種， cross join，inner join，和outer join ， 在 cross join和inner join中，篩選條件放在on后面還是where后面是沒區(qū)別的，極端一點(diǎn)，在編寫這兩種連接查詢的時(shí)候，只用on不使用where也沒有什么問題。因此，on篩選和where篩選的差別只是針對(duì)outer join，也就是平時(shí)最常使用的left join和right join。

下面話不多說，來一起看看詳細(xì)的介紹：

來看一個(gè)示例，有兩張數(shù)據(jù)表，結(jié)構(gòu)和數(shù)據(jù)如圖所示

表main

表ext

可以把這兩張表看作是用來存放用戶信息的， main放置主要信息，ext表放置附加信息，兩張表的關(guān)系是1對(duì)1的，以id字符作為對(duì)應(yīng)關(guān)系鍵?，F(xiàn)在我們需要將地址不為杭州的所有用戶信息篩選出來，結(jié)果中需要包含main表和ext表的所有字段數(shù)據(jù)。

select * from main left JOIN exton main.id = ext.id and address <> '杭州'

閉上眼睛， 請(qǐng)用大腦人肉運(yùn)行一下這段SQL， 想象一下是什么結(jié)果。

當(dāng)把address <> '杭州'這個(gè)篩選條件放在on之后，查詢得到的結(jié)果似乎跟我們預(yù)料中的不同，從結(jié)果中能看出，這個(gè)篩選條件好像只過濾掉了ext表中對(duì)應(yīng)的記錄，而main表中的記錄并沒有被過濾掉，也就是上圖中標(biāo)記為紅色的那條記錄。outer join相對(duì)于inner join的一個(gè)主要特性就是以一側(cè)的表為基礎(chǔ)，但是在這里以左表為基這一點(diǎn)卻可以無視篩選條件，這未免也太霸道了一些。

把查詢語句稍微改動(dòng)一下，將地址的篩選條件從on轉(zhuǎn)移至where

select * from main left JOIN ext on main.id = ext.id where address <> '杭州'

結(jié)果就如我們預(yù)期的那樣了

造成這種結(jié)果上的差異要從outer join查詢的邏輯查詢的各個(gè)階段說起。

總的來說，outer join 的執(zhí)行過程分為4步

      1、先對(duì)兩個(gè)表執(zhí)行交叉連接(笛卡爾積)

      2、應(yīng)用on篩選器

      3、添加外部行

      4、應(yīng)用where篩選器

就拿上面不使用where篩選器的sql來說，執(zhí)行的整個(gè)詳細(xì)過程如下

第一步，對(duì)兩個(gè)表執(zhí)行交叉連接，結(jié)果如下，這一步會(huì)產(chǎn)生36條記錄（此圖顯示不全）

第二步，應(yīng)用on篩選器。篩選器中有兩個(gè)條件，main.id = ext.id and address<> '杭州'，符合要求的記錄如下

這似乎正是我們期望中查詢的結(jié)果，然而在接下來的步驟中這個(gè)結(jié)果會(huì)被打亂

第三步，添加外部行。outer join有一個(gè)特點(diǎn)就是以一側(cè)的表為基，假如另一側(cè)的表沒有符合on篩選條件的記錄，則以null替代。在這次的查詢中，這一步的作用就是將那條原本應(yīng)該被過濾掉的記錄給添加了回來

是不是不種畫蛇添足的感覺, 結(jié)果就成了這樣

第四步，應(yīng)用where篩選器

在這條問題sql中，因?yàn)闆]有where篩選器，所以上一步的結(jié)果就是最終的結(jié)果了。

而對(duì)于那條地址篩選在where條件中的sql，這一步便起到了作用，將所有地址不屬于杭州的記錄篩選了出來

通過上面的講解，已經(jīng)能反應(yīng)出在outer join中的篩選條件在on中和where中的區(qū)別，開發(fā)人員如能詳細(xì)了解之中差別，能規(guī)避很多在編寫sql過程中出現(xiàn)的莫名其妙的錯(cuò)誤。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。

最新評(píng)論