join 是 SQL查詢中很常見的一種操作，具體來講有join，left join, right join，full join等很多形式。具體的原理如下圖所示。但其中最常見的還是使用left join 。

本文代碼在mysql和hive中均測(cè)試通過，代碼本身難度和長度都不大，我準(zhǔn)備了測(cè)試數(shù)據(jù)的mysql和hive代碼，如果覺得有必要，你可以在后臺(tái)回復(fù)“left”獲取，方便自己修改和練習(xí)。

left join 通俗的解釋：以左表為主表，返回左表的所有行，如果右表中沒有匹配，則依然會(huì)有左表的記錄，右表字段用null填充。看起來非常好理解，但實(shí)際操作的過程中可能會(huì)有一些很容易被忽略的點(diǎn)。

一、left join 之后的記錄有幾條

關(guān)于這一點(diǎn)，是要理解left join執(zhí)行的條件。在A join B的時(shí)候，我們?cè)趏n語句里指定兩表關(guān)聯(lián)的鍵。只要是符合鍵值相等的，都會(huì)出現(xiàn)在結(jié)果中。這里面有一對(duì)一，一對(duì)多，多對(duì)多等幾種情況。我們用例子來說明。

1、一對(duì)一

這種情況最好理解。t_name表，有id，name(用戶名稱)，sex(性別)，dt(注冊(cè)日期)等字段。t_age表。有id，age(年齡)，province(省份)，dt(更新日期)等字段。表中包含的信息如下：

現(xiàn)在我們進(jìn)行t_name(左表，別名a)和t_age(右表，別名b)的left join 操作，關(guān)聯(lián)鍵為id。a表有6條記錄，b表有3條記錄，且關(guān)鍵的鍵是唯一的，因此最終結(jié)果以a表為準(zhǔn)有6條記錄，b表有3條關(guān)聯(lián)不上，相應(yīng)的記錄中，b表所有的字段都為空。

2、一對(duì)多

這回我們用t_age作為左表，關(guān)聯(lián)條件為dt。重點(diǎn)看dt為20190905的記錄。由于右表有3條20190905，這三條在關(guān)聯(lián)的時(shí)候都滿足關(guān)聯(lián)條件，因此最終的結(jié)果會(huì)有3條記錄是20190905。

這回為準(zhǔn)的表是t_age表，但顯然結(jié)果并不是原本的3條記錄，而是7條：20190905 3條，20190906 4條。如果你不太理解，可以繼續(xù)往下看。

3、多對(duì)多

上面例子中，20190906的記錄最終有4條，同樣是因?yàn)闈M足了關(guān)聯(lián)條件，是一種2對(duì)2的情況。這里我們還是回到t_name表做主表的情況，用dt來關(guān)聯(lián)?？梢灶A(yù)見，與2中相比，這次結(jié)果中會(huì)多一行20190907的，而b表相應(yīng)的字段依然為空。

2和3中我們看到了一對(duì)多和多對(duì)多的情況，其實(shí)前者是后者的特例。我們只是很簡(jiǎn)要的把兩個(gè)表關(guān)聯(lián)之后所有的字段都列出來了，但實(shí)際中可能需要做一些統(tǒng)計(jì)，聚合等。這里提醒大家在寫關(guān)聯(lián)條件之前，最好思考一下最終的結(jié)果是什么樣的，最終可能有幾行，會(huì)不會(huì)在計(jì)數(shù)的時(shí)候多統(tǒng)計(jì)，哪些行可能會(huì)存在空值，哪些字段可能會(huì)存在空值等。不要因?yàn)橄氘?dāng)然而犯了錯(cuò)誤。這里算是拋磚引玉，感興趣的同學(xué)可以看看這篇博客，進(jìn)一步學(xué)習(xí)，

https://www.cnblogs.com/qdhxhz/p/10897315.html

二、left join 的執(zhí)行原理

接下來我們進(jìn)一步看一下連接條件寫在on里和寫在where里的區(qū)別。在這之前，我們可以看看left join的具體執(zhí)行邏輯。我參考了網(wǎng)上以為大神的博客：

https://developer.aliyun.com/article/718897，總結(jié)如下

mysql采用嵌套循環(huán)的方式處理left join。

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ǔ)上的行  
     }           
    }  
   }   

如果代碼看不懂，直接看結(jié)論就好：

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

我們?cè)賮砜纯磳?shí)例，返回來研究這段話可能更好理解一些。

1、只有1個(gè)on條件

這里可以直接看第一部分中的例子。最終會(huì)輸出以左表為準(zhǔn)，右表匹配不上補(bǔ)null的結(jié)果，但可能會(huì)有多對(duì)多的情況。

2、有2個(gè)on條件

上圖是在關(guān)聯(lián)條件中增加了b.age=24之后的輸出結(jié)果。由于對(duì)b表進(jìn)行了限制，滿足條件的只有一個(gè)，但是由于沒有where條件，因此依然要以左表為準(zhǔn)，又因?yàn)槭且粚?duì)一，所以輸出還是左表的記錄數(shù)。更極端的，我們可以“清空”b表。

以上兩種情況，在b表中都沒有符合條件的結(jié)果，因此在以左表為準(zhǔn)的基礎(chǔ)上，右邊的所有字段都為空。

3、有where的情況

將b.age=24寫到where里，發(fā)現(xiàn)結(jié)果中只有這一行，打破了“left join”以左表為主的限制。同樣再來看下后兩種情況寫到where里會(huì)發(fā)生什么：

沒錯(cuò)，結(jié)果全部是為空的。因?yàn)閣here 在 on 后面執(zhí)行，而on生成的結(jié)果里沒有滿足條件的記錄！

這里給出兩個(gè)結(jié)論：

    1、 on條件是在生成臨時(shí)表時(shí)使用的條件，它不管on中的條件是否為真，都會(huì)返回左邊表中的記錄。

    2、where條件是在臨時(shí)表生成好后，再對(duì)臨時(shí)表進(jìn)行過濾的條件。這時(shí)已經(jīng)沒有l(wèi)eft join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。

4、有is null 或者有 is not null的情況

當(dāng)條件寫在on中：

當(dāng)條件寫在where 中：

直觀的我們理解，WHERE … IS NULL 子句將從匹配階段后的數(shù)據(jù)中過濾掉不滿足匹配條件的數(shù)據(jù)行。對(duì)于條件寫在on中的情況，又可以說，is null是否定匹配條件，is not null是肯定匹配條件。對(duì)于條件寫在where中的，其實(shí)相比之下更容易理解，要看已有的where條件產(chǎn)生的結(jié)果是什么。讀者可以從上面的例子中思考一下。

三、看兩個(gè)實(shí)際案例

經(jīng)過上面的討論，我們來看兩個(gè)案例，進(jìn)一步理解和思考一下left join 的用法。

1、案例1

這個(gè)案例來自于一篇網(wǎng)絡(luò)博客，前文有提到。鏈接：

https://developer.aliyun.com/article/718897

大家可以先思考一下怎么寫再到原文看答案。事實(shí)上，每個(gè)需求都很容易有兩種寫法，區(qū)別就在于條件是寫在where中還是寫在on中。判斷的原則就是我們需要保證結(jié)果中數(shù)據(jù)不缺失也不多余。需求1的條件需要寫在on中(保證結(jié)果不缺失)，需求2的條件需要寫在where中(保證結(jié)果不多余)。

2、案例2

假設(shè)現(xiàn)在有一個(gè)用戶活躍表t_active，記錄了每天活躍的uid和相應(yīng)的活躍日期?，F(xiàn)在想要看距離某一天日期差為0天，1天，2天，3天…活躍的用戶在當(dāng)天還有多少活躍(也就是一個(gè)留存的概念)。期望得到的如下表所示：

對(duì)于表中數(shù)據(jù)，我們可以這樣理解。距離2019-09-29 0天(也就是2019-09-29)的活躍人數(shù)為100,2019-09-29當(dāng)天活躍的還剩100，距離2019-09-29 1天(也就是2019-09-28)的活躍人數(shù)為80,2019-09-29當(dāng)天還剩60。以此類推。

對(duì)于這個(gè)需求，我們可以使用left join進(jìn)行自關(guān)聯(lián)，用之前活躍的天作為左表，最終期望計(jì)算的天作為右表，計(jì)算日期差，并進(jìn)行左右表分別計(jì)數(shù)。初步的SQL如下：(數(shù)據(jù)是自己編的)

在往下看之前請(qǐng)確認(rèn)你理解了需求目標(biāo)，并先思考下，以上的寫法有問題嗎？能否得到上面期望的結(jié)果？

原始數(shù)據(jù)和這段SQL運(yùn)行的結(jié)果如下：

運(yùn)行結(jié)果中出現(xiàn)了dt和datediff為null的情況，你能想象的到這是為什么嗎？而且當(dāng)dt不為null的時(shí)候，最后兩列的數(shù)據(jù)是相同的，顯然和我們的預(yù)期不符。這是什么原因呢？我們來逐步看一下。

首先，我們使用left join 的方式應(yīng)該是沒有問題的，我們先看看不加任何計(jì)算的，select * 的結(jié)果是啥。

可以看到，這相當(dāng)于是前文提到的不加where 條件的一對(duì)一關(guān)聯(lián)，結(jié)果會(huì)以左表為準(zhǔn)，關(guān)聯(lián)不上的用null補(bǔ)齊。值得注意的是，關(guān)聯(lián)不上的日期是null值，而null值在參與datediff的計(jì)算時(shí)，結(jié)果會(huì)是null。到這里你是不是明白一點(diǎn)了。由于null值參與計(jì)算，導(dǎo)致最終datediff 有null值，并且計(jì)數(shù)的時(shí)候，由于null值存在，最終用日期差作為維度的時(shí)候，導(dǎo)致左表和右表的數(shù)量是一樣的。如下面代碼所示：

從上面的結(jié)果我們可以推演出最開始的SQL運(yùn)行結(jié)果。例如，datediff=5的時(shí)候，共兩條記錄，左表右表的count(distinct uid)都為2。datediff為null的時(shí)候，左表結(jié)果為7，右表為0，其他的以此類推，和前面的結(jié)果是一樣的。這樣我們就知道了，沒有達(dá)到預(yù)期的根源在于存在空的日期。那么怎么解決這個(gè)問題呢，顯然就是把空日期填補(bǔ)上就可以了。可以使用case when 當(dāng)右表日期關(guān)聯(lián)不上的時(shí)候，用相應(yīng)日期補(bǔ)足。代碼如下：

可以看到最終得到了想要的結(jié)果，以最后一行為例，它表示，距離2019-09-29 5天的那天(也就是2019-09-24)活躍的人數(shù)有5個(gè)，那些人在2019-09-29仍然活躍的有2人，你可以看一下明細(xì)數(shù)據(jù)核對(duì)一下。其余的以此類推。我們使用case when 把日期寫死了，這個(gè)是建立在我們知道是哪天的基礎(chǔ)上的。實(shí)際中可能是一個(gè)變量，但一定也是一個(gè)固定的值，需要具體情況具體分析。

四、總結(jié)

本文我們學(xué)習(xí)了left join的原理和實(shí)踐中可能會(huì)遇到的問題。包括關(guān)聯(lián)時(shí)結(jié)果中的記錄數(shù)，關(guān)聯(lián)條件寫在on和where中的區(qū)別，where語句中存在is null的時(shí)候如何理解，最后用實(shí)例幫助大家進(jìn)行理解。在此過程中參考了網(wǎng)上的一些博客，大家可以在閱讀本文的基礎(chǔ)上進(jìn)行查閱。希望對(duì)你有所幫助！后臺(tái)回復(fù)“left”，可以獲取本文測(cè)試所用的數(shù)據(jù)集合代碼。