背景
left join在我們使用mysql查詢的過程中可謂非常常見缴淋，比如博客里一篇文章有多少條評(píng)論敢订、商城里一個(gè)貨物有多少評(píng)論资厉、一條評(píng)論有多少個(gè)贊等等厅缺。但是由于對(duì)join、on宴偿、where等關(guān)鍵字的不熟悉湘捎，有時(shí)候會(huì)導(dǎo)致查詢結(jié)果與預(yù)期不符，所以今天我就來總結(jié)一下窄刘，一起避坑窥妇。
這里我先給出一個(gè)場景，并拋出兩個(gè)問題娩践，如果你都能答對(duì)那這篇文章就不用看了活翩。
假設(shè)有一個(gè)班級(jí)管理應(yīng)用，有一個(gè)表classes翻伺，存了所有的班級(jí)材泄；有一個(gè)表students，存了所有的學(xué)生穆趴，具體數(shù)據(jù)如下（在線SQL：https://www.liaoxuefeng.com/wiki/1177760294764384/1179611432985088）：
SELECT * FROM classes;
id name
1 一班
2 二班
3 三班
4 四班
SELECT * FROM students;
id class_id name gender
1 1 小明 M
2 1 小紅 F
3 1 小軍 M
4 1 小米 F
5 2 小白 F
6 2 小兵 M
7 2 小林 M
8 3 小新 F
9 3 小王 M
10 3 小麗 F
那么現(xiàn)在有兩個(gè)需求：
1脸爱、找出每個(gè)班級(jí)的名稱及其對(duì)應(yīng)的女同學(xué)數(shù)量
2、找出一班的同學(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è)需求都是第一條語句是正確的扫尖，要搞清楚這個(gè)問題白对，就得明白mysql對(duì)于left join的執(zhí)行原理，下節(jié)進(jìn)行展開换怖。
原理
mysql 對(duì)于left join的采用類似嵌套循環(huán)的方式來進(jìn)行從處理甩恼，以下面的語句為例：
SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)
其中P1是on過濾條件，缺失則認(rèn)為是TRUE沉颂，P2是where過濾條件条摸，缺失也認(rèn)為是TRUE
該語句的執(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 過濾條件
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 過濾條件
t:=lt||NULL; // 輸出lt和null補(bǔ)上的行
}
}
}
當(dāng)然彻坛，實(shí)際情況中MySQL會(huì)使用buffer的方式進(jìn)行優(yōu)化顷啼，減少行比較次數(shù)，不過這不影響關(guān)鍵的執(zhí)行流程昌屉，不在本文討論范圍之內(nèi)线梗。
從這個(gè)偽代碼中，我們可以看出兩點(diǎn)：
1怠益、右表限制用ON
如果想對(duì)右表進(jìn)行限制，則一定要在on條件中進(jìn)行瘾婿，若在where中進(jìn)行則可能導(dǎo)致數(shù)據(jù)缺失蜻牢，導(dǎo)致左表在右表中無匹配行的行在最終結(jié)果中不出現(xiàn)，違背了我們對(duì)left join的理解偏陪。因?yàn)閷?duì)左表無右表匹配行的行而言抢呆，遍歷右表后b=FALSE,所以會(huì)嘗試用NULL補(bǔ)齊右表，但是此時(shí)我們的P2對(duì)右表行進(jìn)行了限制笛谦，NULL若不滿足P2(NULL一般都不會(huì)滿足限制條件抱虐，除非IS NULL這種)，則不會(huì)加入最終的結(jié)果中饥脑，導(dǎo)致結(jié)果缺失恳邀。
2懦冰、左表限制用WHERE
如果沒有where條件，無論on條件對(duì)左表進(jìn)行怎樣的限制谣沸，左表的每一行都至少會(huì)有一行的合成結(jié)果刷钢，對(duì)左表行而言，若右表若沒有對(duì)應(yīng)的行乳附，則右表遍歷結(jié)束后b=FALSE内地，會(huì)用一行NULL來生成數(shù)據(jù)，而這個(gè)數(shù)據(jù)是多余的赋除。所以對(duì)左表進(jìn)行過濾必須用where阱缓。
分析總結(jié)
下面展開兩個(gè)需求的錯(cuò)誤語句的執(zhí)行結(jié)果和錯(cuò)誤原因：
需求1
name num
一班 2
二班 1
三班 2
需求1由于在where條件中對(duì)右表限制，導(dǎo)致數(shù)據(jù)缺失（四班應(yīng)該有個(gè)為0的結(jié)果）
需求2
name num
一班 4
二班 0
三班 0
四班 0
需求2由于在on條件中對(duì)左表限制举农，導(dǎo)致數(shù)據(jù)多余（其他班的結(jié)果也出來了荆针，還是錯(cuò)的）
通過上面的問題現(xiàn)象和分析，可以得出了結(jié)論：
在left join語句中并蝗，左表過濾必須放where條件中祭犯，右表過濾必須放on條件中
SQL 看似簡單，其實(shí)也有很多細(xì)節(jié)原理在里面滚停，一個(gè)小小的混淆就會(huì)造成結(jié)果與預(yù)期不符沃粗，所以平時(shí)要注意這些細(xì)節(jié)原理，避免關(guān)鍵時(shí)候出錯(cuò)键畴。
