解決Mysql的left join無效及使用的注意事項說明

Mysql的left join無效及使用

今天寫sql發(fā)現(xiàn)使用left join 沒有把左邊表的數(shù)據(jù)全部查詢出來，讓我郁悶了一會，后來仔細研究了一會才知道自己犯了個常識性的錯誤(我是菜鳥)

這是原sql

這樣的查詢并不能將tb_line這張表的數(shù)據(jù)都查詢出來，好尷尬...

后面我才知道原來當我們進行多表查詢，在執(zhí)行到where之前，會先形成一個臨時表

而on就是臨時表中的條件篩選，使用left join則不管條件是否為真，都會查詢出左邊表的數(shù)據(jù)，條件為假的，則顯示為null

where則是在臨時表生成之后的過濾條件

在第一張圖中，我將tb_vehicle這張表的過濾條件放在where之中，那left join所產(chǎn)生條件為假的數(shù)據(jù)，則會在where 的 v.del_flag='0'中被過濾掉(因為條件為假的數(shù)據(jù)，del_flag都為空)

所以我看似使用了left join ，實際上這樣寫與使用inner join的結(jié)果是一樣的

正確sql如下：

在臨時表中就做好條件篩選，這樣就能夠得到左邊表的數(shù)據(jù)

總結(jié)：

使用left join 并需要做條件查詢的時候，需要仔細斟酌改條件篩選放在on后面還是where后面

Mysql left join 避坑指南

現(xiàn)象

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

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

假設(shè)有一個班級管理應(yīng)用，有一個表classes，存了所有的班級；有一個表students，存了所有的學(xué)生，具體數(shù)據(jù)如下：

SELECT * FROM classes;

SELECT * FROM students;

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

找出每個班級的名稱及其對應(yīng)的女同學(xué)數(shù)量

找出一班的同學(xué)總數(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ù)往下翻 ?。∠冉o出你自己的答案，正確答案就在下面。

~

~

~

答案是兩個需求都是第一條語句是正確的，要搞清楚這個問題，就得明白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中有對應(yīng)的行
  }
  IF (!b) { // 遍歷完RT，發(fā)現(xiàn)lt在RT中沒有有對應(yīng)的行，則嘗試用null補一行
    IF P2(lt,NULL) {// 補上null后滿足 where 過濾條件
      t:=lt||NULL; // 輸出lt和null補上的行
    }         
  }
}

當然，實際情況中MySQL會使用buffer的方式進行優(yōu)化，減少行比較次數(shù)，不過這不影響關(guān)鍵的執(zhí)行流程，不在本文討論范圍之內(nèi)。

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

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

如果沒有where條件，無論on條件對左表進行怎樣的限制，左表的每一行都至少會有一行的合成結(jié)果，對左表行而言，若右表若沒有對應(yīng)的行，則右表遍歷結(jié)束后b=FALSE，會用一行NULL來生成數(shù)據(jù)，而這個數(shù)據(jù)是多余的。所以對左表進行過濾必須用where。

下面展開兩個需求的錯誤語句的執(zhí)行結(jié)果和錯誤原因：

需求1

需求2

需求1由于在where條件中對右表限制，導(dǎo)致數(shù)據(jù)缺失（四班應(yīng)該有個為0的結(jié)果）

需求2由于在on條件中對左表限制，導(dǎo)致數(shù)據(jù)多余（其他班的結(jié)果也出來了，還是錯的）

總結(jié)

通過上面的問題現(xiàn)象和分析，可以得出了結(jié)論：在left join語句中，左表過濾必須放where條件中，右表過濾必須放on條件中，這樣結(jié)果才能不多不少，剛剛好。

SQL 看似簡單，其實也有很多細節(jié)原理在里面，一個小小的混淆就會造成結(jié)果與預(yù)期不符，所以平時要注意這些細節(jié)原理，避免關(guān)鍵時候出錯。

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。