亚洲无码系列,天天综合天天干,www99热,国产精品亚洲二区,九热精品,A级毛片网站,91AV视频,国产性爱三级片

鏈接 | segmentfault.com/a/1190000020458807

現(xiàn)象

left join在我們使用mysql查詢的過(guò)程中可謂非常常見，比如博客里一篇文章有多少條評(píng)論、商城里一個(gè)貨物有多少評(píng)論、一條評(píng)論有多少個(gè)贊等等。但是由于對(duì)join、on、where等關(guān)鍵字的不熟悉，有時(shí)候會(huì)導(dǎo)致查詢結(jié)果與預(yù)期不符，所以今天我就來(lái)總結(jié)一下，一起避坑。

這里我先給出一個(gè)場(chǎng)景，并拋出兩個(gè)問題，如果你都能答對(duì)那這篇文章就不用看了。

假設(shè)有一個(gè)班級(jí)管理應(yīng)用，有一個(gè)表classes，存了所有的班級(jí)；有一個(gè)表students，存了所有的學(xué)生，具體數(shù)據(jù)如下（感謝廖雪峰的在線SQL）：

SELECT * FROM classes;

SELECT * FROM students;

那么現(xiàn)在有兩個(gè)需求：

找出每個(gè)班級(jí)的名稱及其對(duì)應(yīng)的女同學(xué)數(shù)量
找出一班的同學(xué)總數(shù)

對(duì)于需求1，大多數(shù)人不假思索就能想出如下兩種sql寫法，請(qǐng)問哪種是對(duì)的？

SELECT?c.name, count(s.name) as?num?
????FROM?classes c left?join?students s
????on?s.class_id = c.id
????and?s.gender = 'F'
????group?by?c.name

或者

SELECT?c.name, count(s.name) as?num?
????FROM?classes c left?join?students s
????on?s.class_id = c.id
????where?s.gender = 'F'
????group?by?c.name

對(duì)于需求2，大多數(shù)人也可以不假思索的想出如下兩種sql寫法，請(qǐng)問哪種是對(duì)的？

SELECT?c.name, count(s.name) as?num?
????FROM?classes c left?join?students s
????on?s.class_id = c.id
????where?c.name = '一班'?
????group?by?c.name

或者

SELECT?c.name, count(s.name) as?num?
????FROM?classes c left?join?students s
????on?s.class_id = c.id
????and?c.name = '一班'?
????group?by?c.name

請(qǐng)不要繼續(xù)往下翻 ??！先給出你自己的答案，正確答案就在下面。
答案是兩個(gè)需求都是第一條語(yǔ)句是正確的，要搞清楚這個(gè)問題，就得明白mysql對(duì)于left join的執(zhí)行原理，下節(jié)進(jìn)行展開。

根源

mysql 對(duì)于left join的采用類似嵌套循環(huán)的方式來(lái)進(jìn)行從處理，以下面的語(yǔ)句為例：

SELECT?* FROM?LT LEFT?JOIN?RT ON?P1(LT,RT)) WHERE?P2(LT,RT)

其中P1是on過(guò)濾條件，缺失則認(rèn)為是TRUE，P2是where過(guò)濾條件，缺失也認(rèn)為是TRUE，該語(yǔ)句的執(zhí)行邏輯可以描述為：

FOR?each row lt in LT {// 遍歷左表的每一行
??BOOL b = FALSE;
??FOR?each row rt in RT such that P1(lt, rt) {// 遍歷右表每一行，找到滿足join條件的行
????IF?P2(lt, rt) {//滿足 where 過(guò)濾條件
??????t:=lt||rt;//合并行，輸出該行
????}
????b=TRUE;// lt在RT中有對(duì)應(yīng)的行
??}
??IF?(!b) { // 遍歷完RT，發(fā)現(xiàn)lt在RT中沒有有對(duì)應(yīng)的行，則嘗試用null補(bǔ)一行
????IF?P2(lt,NULL) {// 補(bǔ)上null后滿足 where 過(guò)濾條件
??????t:=lt||NULL; // 輸出lt和null補(bǔ)上的行
????}
??}
}

當(dāng)然，實(shí)際情況中MySQL會(huì)使用buffer的方式進(jìn)行優(yōu)化，減少行比較次數(shù)，不過(guò)這不影響關(guān)鍵的執(zhí)行流程，不在本文討論范圍之內(nèi)。從這個(gè)偽代碼中，我們可以看出兩點(diǎn)：

如果想對(duì)右表進(jìn)行限制，則一定要在on條件中進(jìn)行，若在where中進(jìn)行則可能導(dǎo)致數(shù)據(jù)缺失，導(dǎo)致左表在右表中無(wú)匹配行的行在最終結(jié)果中不出現(xiàn)，違背了我們對(duì)left join的理解。因?yàn)閷?duì)左表無(wú)右表匹配行的行而言，遍歷右表后b=FALSE,所以會(huì)嘗試用NULL補(bǔ)齊右表，但是此時(shí)我們的P2對(duì)右表行進(jìn)行了限制，NULL若不滿足P2(NULL一般都不會(huì)滿足限制條件，除非IS NULL這種)，則不會(huì)加入最終的結(jié)果中，導(dǎo)致結(jié)果缺失。
如果沒有where條件，無(wú)論on條件對(duì)左表進(jìn)行怎樣的限制，左表的每一行都至少會(huì)有一行的合成結(jié)果，對(duì)左表行而言，若右表若沒有對(duì)應(yīng)的行，則右表遍歷結(jié)束后b=FALSE，會(huì)用一行NULL來(lái)生成數(shù)據(jù)，而這個(gè)數(shù)據(jù)是多余的。所以對(duì)左表進(jìn)行過(guò)濾必須用where。

下面展開兩個(gè)需求的錯(cuò)誤語(yǔ)句的執(zhí)行結(jié)果和錯(cuò)誤原因：需求1

需求2

需求1由于在where條件中對(duì)右表限制，導(dǎo)致數(shù)據(jù)缺失（四班應(yīng)該有個(gè)為0的結(jié)果）需求2由于在on條件中對(duì)左表限制，導(dǎo)致數(shù)據(jù)多余（其他班的結(jié)果也出來(lái)了，還是錯(cuò)的）

總結(jié)

通過(guò)上面的問題現(xiàn)象和分析，可以得出了結(jié)論：在left join語(yǔ)句中，左表過(guò)濾必須放where條件中，右表過(guò)濾必須放on條件中，這樣結(jié)果才能不多不少，剛剛好。SQL 看似簡(jiǎn)單，其實(shí)也有很多細(xì)節(jié)原理在里面，一個(gè)小小的混淆就會(huì)造成結(jié)果與預(yù)期不符，所以平時(shí)要注意這些細(xì)節(jié)原理，避免關(guān)鍵時(shí)候出錯(cuò)。

- End -

技術(shù)連載目錄（可點(diǎn)擊跳轉(zhuǎn)即可閱讀）：

面試題系列教程??點(diǎn)擊-->?面試題技術(shù)干貨連載目錄?跳轉(zhuǎn)

Maven系列教程??點(diǎn)擊-->?Maven技術(shù)干貨連載目錄?跳轉(zhuǎn)

MyBatis系列教程??點(diǎn)擊-->?MyBatis技術(shù)干貨連載目錄?跳轉(zhuǎn)

JVM調(diào)優(yōu)總結(jié)系列教程??點(diǎn)擊-->?JVM調(diào)優(yōu)技術(shù)干貨連載目錄?跳轉(zhuǎn)

點(diǎn)擊在看,愿你我不再陌生?

MySQL:Left Join 避坑指南

現(xiàn)象

根源

總結(jié)