MySQL：left join 避坑指南技术頭條網

現象

left join在我們使用mysql查詢的過程中可謂非常常見，比如博客裡一篇文章有多少條評論、商城裡一個貨物有多少評論、一條評論有多少個贊等等。但是由於對join、on、where等關鍵字的不熟悉，有時候會導致查詢結果與預期不符，所以今天我就來總結一下，一起避坑。

這裡我先給出一個場景，並拋出兩個問題，如果你都能答對那這篇文章就不用看了。

假設有一個班級管理應用，有一個表classes，存了所有的班級；有一個表students，存了所有的學生，具體數據如下（感謝廖雪峰的在線SQL）：

SELECT * FROM classes;

SELECT * FROM students;

那麼現在有兩個需求：

找出每個班級的名稱及其對應的女同學數量

找出一班的同學總數

對於需求1，大多數人不假思索就能想出如下兩種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，大多數人也可以不假思索的想出如下兩種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

請不要繼續往下翻！！先給出你自己的答案，正確答案就在下面。
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~

答案是兩個需求都是第一條語句是正確的，要搞清楚這個問題，就得明白mysql對於left join的執行原理，下節進行展開。

根源

mysql 對於left join的採用類似嵌套循環的方式來進行從處理，以下面的語句為例：

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

其中P1是on過濾條件，缺失則認為是TRUE，P2是where過濾條件，缺失也認為是TRUE，該語句的執行邏輯可以描述為：

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，發現lt在RT中沒有有對應的行，則嘗試用null補一行
 IF P2(lt,NULL) {// 補上null後滿足 where 過濾條件
 t:=lt||NULL; // 輸出lt和null補上的行
 } 
 }
}

當然，實際情況中MySQL會使用buffer的方式進行優化，減少行比較次數，不過這不影響關鍵的執行流程，不在本文討論範圍之內。

從這個偽代碼中，我們可以看出兩點：

如果想對右表進行限制，則一定要在on條件中進行，若在where中進行則可能導致數據缺失，導致左表在右表中無匹配行的行在最終結果中不出現，違背了我們對left join的理解。因為對左表無右表匹配行的行而言，遍歷右表後b=FALSE,所以會嘗試用NULL補齊右表，但是此時我們的P2對右錶行進行了限制，NULL若不滿足P2(NULL一般都不會滿足限制條件，除非IS NULL這種)，則不會加入最終的結果中，導致結果缺失。

如果沒有where條件，無論on條件對左表進行怎樣的限制，左表的每一行都至少會有一行的合成結果，對左錶行而言，若右表若沒有對應的行，則右表遍歷結束後b=FALSE，會用一行NULL來生成數據，而這個數據是多餘的。所以對左表進行過濾必須用where。

下面展開兩個需求的錯誤語句的執行結果和錯誤原因：

需求1