MaxCompute_Sql_Join_開發指南
背景及目的
本文結果都是在SQL標準語義模式下的推導結果,希望大家都能夠按照標準的SQL語義來寫SQL,這樣才能保證後續SQL的可移植性。
MaxCompute SQL中的JOIN ON條件
MaxCompute SQL中,很常用的一個操作就是關聯(Join)。目前MaxCompute提供了一下幾種Join型別:
型別 | 含義 |
---|---|
Inner Join | 輸出符合關聯條件的資料 |
Left Join | 輸出左表的所有記錄,對於右表符合關聯的資料,輸出右表,沒有符合的,右表補NULL |
Right Join | 輸出右表的所有記錄,對於左表符合關聯的資料,輸出左表,沒有符合的,左表補NULL |
Full Join | 輸出左表和右表的所有記錄,對於沒有關聯上的資料,未關聯的另一側補null |
Left Semi Join | 對於左表中的一條資料,如果右表存在符合關聯條件的行,則輸出左表 |
Left Anti Join | 對於左表中的一條資料,如果對於右表所有的行,不存在符合關聯條件的資料,則輸出左表 |
User Defined Join | 指定兩個輸入流,使用者自己實現Join的邏輯,這裡不展開討論 |
根據不同的場景,使用者可以使用不同的Join型別來實現對應的關聯操作。但是在實際使用過程當中,經常有使用者分不清楚過濾條件在JOIN ON語句中還是在WHERE中有什麼區別,或者認為他們的效果都是一樣的,例如在生產的環境中經常可以看到使用者寫了
A (LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI) JOIN B ON a.key = b.key and A.ds='20190121' and B.ds='20190121';
這裡使用者的本意是希望在A和B中獲取某一個分割槽的資料進行JOIN操作,也就是
(SELECT * FROM A WHERE ds='20190121') A (LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI)JOIN (SELECT * FROM B WHERE ds='20190121') B ON a.key = b.key
然而針對不同的Join型別,兩者可能並不等價,不僅無法將分割槽條件下推,導致全表掃描,而且會導致正確性問題。這裡簡要辨析一下過濾條件分別在以下的的異同
- 子查詢的WHERE條件
- JOIN ON條件
- JOIN ON後的WHERE條件
1 原理
這裡先說明一個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}
來說,計算順序為
- 子查詢中的{subquery_where_condition}
- JOIN的{on_condition}的條件
- JOIN結果集合{where_condition}的計算
對於不同的JOIN型別,濾語句放在{subquery_where_condition}、{on_condition}和{where_condition}中,有時結果是一致的,有時候結果又是不一致的。下面分情況進行討論:
2 實驗
2.1 準備
首先構造表A
CREATE TABLE A AS SELECT * FROM VALUES (1, 20190121),(2, 20190121),(2, 20190122) t (key, ds);
key | ds |
---|---|
1 | 20190121 |
2 | 20190121 |
2 | 20190122 |
則他們的笛卡爾乘積為
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
1 | 20190121 | 3 | 20190121 |
1 | 20190121 | 2 | 20190122 |
2 | 20190121 | 1 | 20190121 |
2 | 20190121 | 3 | 20190121 |
2 | 20190121 | 2 | 20190122 |
2 | 20190122 | 1 | 20190121 |
2 | 20190122 | 3 | 20190121 |
2 | 20190122 | 2 | 20190122 |
2.2 Inner Join
結論:過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}中都是等價的。
Inner Join的處理邏輯是將左右表進行笛卡爾乘積,然後選擇滿足ON表示式的行進行輸出。
第一種情況,子查詢中過濾:
SELECT A.*, B.* FROM (SELECT * FROM A WHERE ds='20190121') A JOIN (SELECT * FROM B WHERE ds='20190121') B ON a.key = b.key;
非常簡單,結果只有一條
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
第二種情況,JOIN 條件中過濾
SELECT A.*, B.* FROM A JOIN B ON a.key = b.key and A.ds='20190121' and B.ds='20190121';
笛卡爾積的結果有9條,滿足ON條件的結果同樣只有1條。
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
第三種情況,JOIN後的WHERE條件過濾
SELECT A.*, B.* FROM A JOIN B ON a.key = b.key WHERE A.ds='20190121' and B.ds='20190121';
來說,笛卡爾積的結果有9條,滿足ON條件a.key = b.key的結果有3條,分別是
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
2 | 20190121 | 2 | 20190122 |
2 | 20190122 | 2 | 20190122 |
此時對於這個結果再進行過濾A.ds='20190121' and B.ds='20190121',結果只有1條,和剛才的結果一致
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
2.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。
第一種情況,子查詢中過濾:
SELECT A.*, B.* FROM (SELECT * FROM A WHERE ds='20190121') A LEFT JOIN (SELECT * FROM B WHERE ds='20190121') B ON a.key = b.key; 過濾後,左右側有兩條,右側有一條,結果有兩條
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
2 | 20190121 | NULL | NULL |
第二種情況,JOIN 條件中過濾
SELECT A.*, B.* FROM A LEFT JOIN B ON a.key = b.key and A.ds='20190121' and B.ds='20190121';
笛卡爾積的結果有9條,滿足ON條件的結果同樣只有1條,則對於左表剩餘的兩條輸出左表,右表補NULL。
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
2 | 20190121 | NULL | NULL |
2 | 20190122 | NULL | NULL |
第三種情況,JOIN後的WHERE條件過濾:
SELECT A.*, B.* FROM A LEFT JOIN B ON a.key = b.key WHERE A.ds='20190121' and B.ds='20190121';
來說,笛卡爾積的結果有9條,滿足ON條件a.key = b.key的結果有3條,分別是
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
2 | 20190121 | 2 | 20190122 |
2 | 20190122 | 2 | 20190122 |
此時對於這個結果再進行過濾A.ds='20190121' and B.ds='20190121',結果只有1條
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
可以看到,將過濾條件放在三個不同的地方,得到了三種不同的結果。
2.4 Right Join
Right Join和Left Join是類似的,只是左右表的區別。
結論:過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}不一定等價。
對於右表的過濾條件,放在{subquery_where_condition}和{where_condition}是等價的。
對於左表的過濾條件,放在{subquery_where_condition}和{on_condition}中是等價的。
2.5 Full Join
結論:過濾條件寫在{subquery_where_condition}、{on_condition}和{where_condition}均不等價。
FULL Join的處理邏輯是將左右表進行笛卡爾乘積,然後對於滿足ON表示式的行進行輸出,對於兩側表中不滿足ON表示式的行,輸出有資料的表,另一側補NULL。
第一種情況,子查詢中過濾:
SELECT A.*, B.* FROM (SELECT * FROM A WHERE ds='20190121') A FULL JOIN (SELECT * FROM B WHERE ds='20190121') B ON a.key = b.key;
過濾後,左右側有兩條,右側有兩條,結果有三條
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
2 | 20190121 | NULL | NULL |
NULL | NULL | 3 | 20190121 |
第二種情況,JOIN 條件中過濾:
SELECT A.*, B.* FROM A FULL JOIN B ON a.key = b.key and A.ds='20190121' and B.ds='20190121';
笛卡爾積的結果有9條,滿足ON條件的結果同樣只有1條,則對於左表剩餘的兩條輸出左表,右表補NULL。右表剩餘的兩條輸出右表,左表補NULL
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
2 | 20190121 | NULL | NULL |
2 | 20190122 | NULL | NULL |
NULL | NULL | 3 | 20190121 |
NULL | NULL | 2 | 20190122 |
第三種情況,JOIN後的WHERE條件過濾
SELECT A.*, B.* FROM A FULL JOIN B ON a.key = b.key WHERE A.ds='20190121' and B.ds='20190121';
笛卡爾積的結果有9條,滿足ON條件a.key = b.key的結果有3條,分別是
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
2 | 20190121 | 2 | 20190122 |
2 | 20190122 | 2 | 20190122 |
再對沒有JOIN上的資料進行輸出,另一側補NULL,得到結果
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
2 | 20190121 | 2 | 20190122 |
2 | 20190122 | 2 | 20190122 |
NULL | NULL | 3 | 20190121 |
此時對於這個結果再進行過濾A.ds='20190121' and B.ds='20190121',結果只有1條
a.key | a.ds | b.key | b.ds |
---|---|---|---|
1 | 20190121 | 1 | 20190121 |
可以看到,和LEFT JOIN類似,得到了三種不同的結果。
2.6 Left Semi Join
結論:過濾條件寫在{subquery_where_condition}、{on_condition}和{where_condition}是等價的。
LEFT SEMI Join的處理邏輯是對於左表的每一條記錄,都去和右表進行匹配,如果匹配成功,則輸出左表。這裡需要注意的是由於只輸出左表,所以JOIN後的Where條件中不能寫右側的過濾條件。LEFT SEMI JOIN常常用來實現exists的語義
第一種情況,子查詢中過濾:
SELECT A.* FROM (SELECT * FROM A WHERE ds='20190121') A LEFT SEMI JOIN (SELECT * FROM B WHERE ds='20190121') B ON a.key = b.key;
過濾後,左右側有兩條,最終符合a.key = b.key的只有一條
| a.key | a.ds |
| -------- | -------- |
| 1 |20190121 |
第二種情況,JOIN 條件中過濾:
SELECT A.* FROM A LEFT SEMI JOIN B ON a.key = b.key and A.ds='20190121' and B.ds='20190121';
對於左側的三條記錄,滿足ON條件的結果同樣只有1條
| a.key| a.ds|
| -------- | -------- |
|1| 20190121 |
第三種情況,JOIN後的WHERE條件過濾
SELECT A.* FROM A LEFT SEMI JOIN (SELECT * FROM B WHERE ds='20190121') B ON a.key = b.key WHERE A.ds='20190121';
左側能符合ON條件的有一條
| a.key| a.ds|
| -------- | -------- |
|1 |20190121 |
此時對於這個結果再進行過濾A.ds='20190121',結果仍然保持1條
| a.key| a.ds|
| -------- | -------- |
| 1 |20190121 |
可以看到,LEFT SEMI JOIN和INNER JOIN類似,無論過濾條件放在哪裡,結果都是一致的。
2.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的語義。
第一種情況,子查詢中過濾:
SELECT A.* FROM (SELECT * FROM A WHERE ds='20190121') A LEFT ANTI JOIN (SELECT * FROM B WHERE ds='20190121') B ON a.key = b.key;
過濾後,左側有兩條,右側有兩條,結果有1條
a.key | a.ds |
---|---|
2 | 20190121 |
第二種情況,JOIN 條件中過濾:
SELECT A.* FROM A LEFT ANTI JOIN B ON a.key = b.key and A.ds='20190121' and B.ds='20190121';
對於左側的三條記錄,只有第一條有滿足ON條件的結果,所以輸出剩餘的兩條記錄
a.key | a.ds |
---|---|
2 | 20190121 |
2 | 20190122 |
第三種情況,JOIN後的WHERE條件過濾
SELECT A.* FROM A LEFT ANTI JOIN (SELECT * FROM B WHERE ds='20190121') B ON a.key = b.key WHERE A.ds='20190121';
左側能通過ON條件的有兩條
a.key | a.ds |
---|---|
2 | 20190121 |
2 | 20190122 |
此時對於這個結果再進行過濾A.ds='20190121',結果為1條
a.key | a.ds |
---|---|
2 | 20190121 |
可以看到,LEFT ANTI JOIN中,過濾條件放在JOIN ON條件中和前後的WHERE條件中,結果是不相同的。
以上只是針對一個常用場景的幾種不同的寫法做的簡單的測試,沒有具體的推導過程,對於涉及到不等值表示式的場景會更加複雜,有興趣的同學可以自己嘗試推導一下。
3 總結
過濾條件放在不同的位置語義可能大不相同,對於使用者而言,如果只是進行過濾資料後再JOIN的操作,可以簡要記住以下幾點,當然如果還是覺得規則比較複雜的話,那最好的方法就是每次都把過濾條件寫到子查詢中,雖然這樣寫起來會囉嗦一些。
- INNER JOIN/LEFT SEMI JOIN 對於兩側的表示式可以隨便寫。
- LEFT JOIN/LEFT ANTI JOIN 左表的過濾條件要放到{subquery_where_condition}或者{where_condition},右表的過濾條件要放到{subquery_where_condition}或者{on_condition}中。
- RIGHT JOIN和LEFT JOIN相反,右表的過濾條件要放到{subquery_where_condition}或者{where_condition},左表的過濾條件要放到{subquery_where_condition}或者{on_condition}。
- FULL OUTER JOIN 只能放到{subquery_where_condition}中。