MaxCompute(ODPS) SQL中，很常用的一個操作就是關聯(Join)。

概述

目前MaxCompute提供了以下幾種Join類型：

類型 含義
Inner Join 輸出符合關聯條件的資料
Left Join 輸出左表的所有記錄，對於右表符合關聯的資料，輸出右表，沒有符合的，右表補null。
Right Join 輸出右表的所有記錄，對於左表符合關聯的資料，輸出左表，沒有符合的，左表補null。
Full Join 輸出左表和右表的所有記錄，對於沒有關聯上的資料，未關聯的另一側補null。
Left Semi Join 對於左表中的一條資料，如果右表存在符合關聯條件的行，則輸出左表。
Left Anti Join 對於左表中的一條資料，如果對於右表所有的行，不存在符合關聯條件的資料，則輸出左表。
根據不同的情境，使用者可以使用不同的Join類型來實現對應的關聯操作。但是在實際使用過程中，存在這樣的錯誤樣本： 
A (LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI) JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';
這裡使用者的本意是希望在A和B中擷取某一個分區的資料進行JOIN操作，也就是： 
(SELECT * FROM A WHERE ds='20180101') A
(LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI)  JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key

然而針對不同的Join類型，兩者可能並不等價，不僅無法將分區條件下推，導致全表掃描，而且會導致正確性問題。這裡簡要辨析一下過濾條件分別在：

  1. 子查詢的WHERE條件。
  2. JOIN ON條件。
  3. JOIN ON後的WHERE條件。

原理

這裡先說明一個JOIN和WHERE條件的計算順序，對於：

(SELECT * FROM A WHERE {subquery_where_condition} A) A
JOIN
(SELECT * FROM B WHERE {subquery_where_condition} B) B
ON {on_condition}
WHERE {where_condition}

來說，計算順序為：

  1. 子查詢中的{subquery_where_condition}
  2. JOIN的{on_condition}的條件
  3. JOIN結果集合{where_condition}的計算

對於不同的JOIN類型，過濾語句放在{subquery_where_condition}、{on_condition}和{where_condition}中，有時結果是一致的，有時候結果又是不一致的。下面分情況進行討論。

實驗

  1. 準備

    首先構造表A：

    CREATE TABLE A AS SELECT * FROM VALUES (1, 20180101),(2, 20180101),(2, 20180102) t (key, ds);
    key ds
    1 20180101
    2 20180101
    2 20180102

    表B：

    CREATE TABLE B AS SELECT * FROM VALUES (1, 20180101),(3, 20180101),(2, 20180102) t (key, ds);
    key ds
    1 20180101
    3 20180101
    2 20180102

    則他們的笛卡爾乘積為：

    a.key a.ds b.key b.ds
    1 20180101 1 20180101
    1 20180101 3 20180101
    1 20180101 2 20180102
    2 20180101 1 20180101
    2 20180101 3 20180101
    2 20180101 2 20180102
    2 20180102 1 20180101
    2 20180102 3 20180101
    2 20180102 2 20180102
  2. Inner Join

    結論：過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}中都是等價的。

    Inner Join的處理邏輯是將左右表進行笛卡爾乘積，然後選擇滿足ON運算式的行進行輸出。

    1. 第一種情況，子查詢中過濾：
      SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

      非常簡單，結果只有一條：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101
    2. 第二種情況，JOIN 條件中過濾：
      SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

      笛卡爾積的結果有9條，滿足ON條件的結果同樣只有1條：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101
    3. 第三種情況，JOIN後的WHERE條件過濾：
      SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

      來說，笛卡爾積的結果有9條，滿足ON條件a.key = b.key的結果有3條，分別是：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101
      2 20180102 2 20180102
      2 20180101 2 20180102

      此時對於這個結果再進行過濾A.ds='20180101' and B.ds='20180101'，結果只有1條：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101

      可以看到，將過濾條件放在三個不同的地方，得到了三種不同的結果。

  3. Left Join

    結論：過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}不一定等價。

    對於左表的過濾條件，放在{subquery_where_condition}和{where_condition}是等價的。

    對於右表的過濾條件，放在{subquery_where_condition}和{on_condition}中是等價的。

    Left Join的處理邏輯是將左右表進行笛卡爾乘積，然後對於滿足ON運算式的行進行輸出，對於左表中不滿足ON運算式的行，輸出左表，右表補NULL。

    1. 第一種情況，子查詢中過濾：
      SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

      過濾後，左右側有兩條，右側有一條，結果有兩條：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101
      2 20180101 NULL NULL
    2. 第二種情況，JOIN 條件中過濾：
      SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

      笛卡爾積的結果有9條，滿足ON條件的結果同樣只有1條，則對於左表剩餘的兩條輸出左表，右表補NULL：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101
      2 20180101 NULL NULL
      2 20180102 NULL NULL
    3. 第三種情況，JOIN後的WHERE條件過濾：
      SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

      來說，笛卡爾積的結果有9條，滿足ON條件a.key = b.key的結果有3條，分別是：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101
      2 20180101 2 20180102
      2 20180102 2 20180102

      此時對於這個結果再進行過濾A.ds='20180101' and B.ds='20180101'，結果只有1條：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101

      可以看到，將過濾條件放在三個不同的地方，得到了三種不同的結果。

  4. Right Join

    Right Join和Left Join是類似的，只是左右表的區別。結論：過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}不一定等價。對於右表的過濾條件，放在{subquery_where_condition}和{where_condition}是等價的。對於左表的過濾條件，放在{subquery_where_condition}和{on_condition}中是等價的。

  5. Full Join

    結論：過濾條件寫在{subquery_where_condition}、{on_condition}和{where_condition}均不等價。

    FULL Join的處理邏輯是將左右表進行笛卡爾乘積，然後對於滿足ON運算式的行進行輸出，對於兩側表中不滿足ON運算式的行，輸出有資料的表，另一側補NULL。

    1. 第一種情況，子查詢中過濾：
      SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
FULL JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

      過濾後，左右側有兩條，右側有兩條，結果有三條：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101
      2 20180101 NULL NULL
      NULL NULL 3 20180101
    2. 第二種情況，JOIN 條件中過濾：
      SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

      笛卡爾積的結果有9條，滿足ON條件的結果同樣只有1條，則對於左表剩餘的兩條輸出左表，右表補NULL。右表剩餘的兩條輸出右表，左表補NULL：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101
      2 20180101 NULL NULL
      2 20180102 NULL NULL
      NULL NULL 3 20180101
      NULL NULL 2 20180102
    3. 第三種情況，JOIN後的WHERE條件過濾：
      SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

      笛卡爾積的結果有9條，滿足ON條件a.key = b.key的結果有3條，分別是：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101
      2 20180101 2 20180102
      2 20180102 2 20180102

      再對沒有JOIN上的資料進行輸出，另一側補NULL，得到結果：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101
      2 20180101 2 20180102
      2 20180102 2 20180102
      NULL NULL 3 20180101

      此時對於這個結果再進行過濾A.ds='20180101' and B.ds='20180101'，結果只有1條：

      a.key a.ds b.key b.ds
      1 20180101 1 20180101

      可以看到，和LEFT JOIN類似，得到了三種不同的結果。

  6. Left Semi Join

    結論：過濾條件寫在{subquery_where_condition}、{on_condition}和{where_condition}是等價的。

    LEFT SEMI Join的處理邏輯是對於左表的每一條記錄，都去和右表進行匹配，如果匹配成功，則輸出左表。這裡需要注意的是由於只輸出左表，所以JOIN後的Where條件中不能寫右側的過濾條件。LEFT SEMI JOIN常用來實現exists的語義：

    1. 第一種情況，子查詢中過濾：
      SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

      過濾後，左右側有兩條，最終符合a.key = b.key的只有一條：

      a.key a.ds
      1 20180101
    2. 第二種情況，JOIN 條件中過濾：
      SELECT A.*
FROM A LEFT SEMI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

      對於左側的三條記錄，滿足ON條件的結果同樣只有1條：

      a.key a.ds
      1 20180101
    3. 第三種情況，JOIN後的WHERE條件過濾：
      SELECT A.*
FROM A LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key
WHERE A.ds='20180101';

      左側能符合ON條件的有一條：

      a.key a.ds
      1 20180101

      此時對於這個結果再進行過濾A.ds='20180101'，結果仍然保持1條：

      a.key a.ds
      1 20180101

      可以看到，LEFT SEMI JOIN和INNER JOIN類似，無論過濾條件放在哪裡，結果都是一致的。

  7. Left Anti Join

    結論：過濾條件寫在{subquery_where_condition}、{on_condition}和{where_condition}不一定等價。

    對於左表的過濾條件，放在{subquery_where_condition}和{where_condition}是等價的。

    對於右表的過濾條件，放在{subquery_where_condition}和{on_condition}中是等價的，右表運算式不能放在{where_condition}中。

    LEFT ANTI Join的處理邏輯是對於左表的每一條記錄，都去和右表進行匹配，如果右表所有的記錄都沒有匹配成功，則輸出左表。同樣由於只輸出左表，所以JOIN後的Where條件中不能寫右側的過濾條件。LEFT SEMI JOIN常常用來實現not exists的語義。

    1. 第一種情況，子查詢中過濾：
      SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

      過濾後，左側有兩條，右側有兩條，結果有1條：

      a.key a.ds
      2 20180101
    2. 第二種情況，JOIN 條件中過濾：
      SELECT A.*
FROM A LEFT ANTI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

      對於左側的三條記錄，只有第一條有滿足ON條件的結果，所以輸出剩餘的兩條記錄：

      a.key a.ds
      2 20180101
      2 20180102
    3. 第三種情況，JOIN後的WHERE條件過濾：
      SELECT A.*
FROM A LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key
WHERE A.ds='20180101';

      左側能通過ON條件的有兩條：

      a.key a.ds
      2 20180101
      2 20180102
      此時對於這個結果再進行過濾 A.ds='20180101'，結果為1條：
      a.key a.ds
      2 20180101

      可以看到，LEFT ANTI JOIN中，過濾條件放在JOIN ON條件中和前後的WHERE條件中，結果是不相同的。

      以上只是針對一個常用情境的幾種不同的寫法做的簡單的測試，沒有具體的推導過程，對於涉及到不等值運算式的情境會更加複雜，有興趣的同學可以自己嘗試推導一下。

線上狀態

上述結果都是在SQL標準語義模式下的推導結果。有的使用者會發現線上上環境中，相同的語句得到的結果和預期並不相符，這是由於一些曆史原因和相容性的考慮。在OUTER JOIN的實現中，在project層級設定了一個flag，叫做odps.sql.outerjoin.supports.filters，如果這個設定為false，表示OUTER JOIN的ON條件不支援過濾條件，寫在{on_condition}會被當做寫在了{subquery_where_condition}，這是一個非標準的行為。有些使用者在兩個project中切換，發現相同的SQL在兩個project中運行行為不一致，就是由這個引起的。

這裡希望大家都能夠按照標準的SQL語義來寫SQL，這樣才能保證後續SQL的可移植性。

查看project設定可以在 http://adminconsole.odps.aliyun-inc.com/inn.view 中project管理找到對應的project，查看屬性。

總結

過濾條件放在不同的位置語義可能大不相同，對於使用者而言，如果只是進行過濾資料後再JOIN的操作，可以簡要記住以下幾點。

  1. INNER JOIN/LEFT SEMI JOIN 對於兩側的運算式可以隨便寫。
  2. LEFT JOIN/LEFT ANTI JOIN 左表的過濾條件要放到{subquery_where_condition}或者{where_condition}，右表的過濾條件要放到{subquery_where_condition}或者{on_condition}中。
  3. RIGHT JOIN和LEFT JOIN相反，右表的過濾條件要放到{subquery_where_condition}或者{where_condition}，左表的過濾條件要放到{subquery_where_condition}或者{on_condition}。
  4. FULL OUTER JOIN 只能放到{subquery_where_condition}中。

當然如果還是覺得規則比較複雜的話，最好的方法就是每次都把過濾條件寫到子查詢中。