背景
left join
在我們使用mysql查詢的過程中可謂非常常見,比如博客里一篇文章有多少條評論撇他、商城里一個貨物有多少評論茄猫、一條評論有多少個贊等等。但是由于對join
困肩、on
划纽、where
等關鍵字的不熟悉,有時候會導致查詢結果與預期不符锌畸,所以今天我就來總結一下勇劣,一起避坑。
這里我先給出一個場景潭枣,并拋出兩個問題比默,如果你都能答對那這篇文章就不用看了。
假設有一個班級管理應用盆犁,有一個表classes命咐,存了所有的班級;有一個表students谐岁,存了所有的學生醋奠,具體數(shù)據如下(感謝廖雪峰的在線SQL):
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)在有兩個需求:
- 找出每個班級的名稱及其對應的女同學數(shù)量
- 找出一班的同學總數(shù)
對于需求1,大多數(shù)人不假思索就能想出如下兩種sql寫法伊佃,請問哪種是對的钝域?
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
對于需求2,大多數(shù)人也可以不假思索的想出如下兩種sql寫法锭魔,請問哪種是對的?
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
請不要繼續(xù)往下翻 B肺亍迷捧!先給出你自己的答案,正確答案就在下面胀葱。
.
.
.
.
.
.
.
.
答案是兩個需求都是第一條語句是正確的漠秋,要搞清楚這個問題,就得明白mysql對于left join
的執(zhí)行原理抵屿,下節(jié)進行展開庆锦。
根源
mysql 對于left join
的采用類似嵌套循環(huán)的方式來進行從處理,以下面的語句為例:
SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)
其中P1
是on
過濾條件轧葛,缺失則認為是TRUE
搂抒,P2
是where
過濾條件艇搀,缺失也認為是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中有對應的行
}
IF (!b) { // 遍歷完RT,發(fā)現(xiàn)lt在RT中沒有有對應的行芳杏,則嘗試用null補一行
IF P2(lt,NULL) {// 補上null后滿足 where 過濾條件
t:=lt||NULL; // 輸出lt和null補上的行
}
}
}
當然矩屁,實際情況中MySQL會使用buffer的方式進行優(yōu)化,減少行比較次數(shù)爵赵,不過這不影響關鍵的執(zhí)行流程吝秕,不在本文討論范圍之內。
從這個偽代碼中空幻,我們可以看出兩點:
- 如果想對右表進行限制烁峭,則一定要在
on
條件中進行,若在where
中進行則可能導致數(shù)據缺失氛悬,導致左表在右表中無匹配行的行在最終結果中不出現(xiàn)则剃,違背了我們對left join
的理解。因為對左表無右表匹配行的行而言如捅,遍歷右表后b=FALSE
,所以會嘗試用NULL
補齊右表棍现,但是此時我們的P2
對右表行進行了限制,NULL若不滿足P2
(NULL
一般都不會滿足限制條件镜遣,除非IS NULL
這種)己肮,則不會加入最終的結果中,導致結果缺失悲关。 - 如果沒有
where
條件谎僻,無論on
條件對左表進行怎樣的限制,左表的每一行都至少會有一行的合成結果寓辱,對左表行而言艘绍,若右表若沒有對應的行,則右表遍歷結束后b=FALSE
秫筏,會用一行NULL
來生成數(shù)據诱鞠,而這個數(shù)據是多余的。所以對左表進行過濾必須用where这敬。
下面展開兩個需求的錯誤語句的執(zhí)行結果和錯誤原因:
需求1
name num
一班 2
二班 1
三班 2
需求2
name num
一班 4
二班 0
三班 0
四班 0
- 需求1由于在where條件中對右表限制航夺,導致數(shù)據缺失(四班應該有個為
0
的結果) - 需求2由于在on條件中對左表限制,導致數(shù)據多余(其他班的結果也出來了崔涂,還是錯的)
總結
通過上面的問題現(xiàn)象和分析阳掐,可以得出了結論:在left join
語句中,左表過濾必須放where條件中,右表過濾必須放on條件中缭保,這樣結果才能不多不少汛闸,剛剛好。
SQL 看似簡單涮俄,其實也有很多細節(jié)原理在里面蛉拙,一個小小的混淆就會造成結果與預期不符,所以平時要注意這些細節(jié)原理彻亲,避免關鍵時候出錯孕锄。