全部產品
Search

搜尋本產品

    MaxCompute：MaxCompute SQL中的關聯操作

    文件中心

    MaxCompute：MaxCompute SQL中的關聯操作

    更新時間：Jun 19, 2024

    當您編寫MaxCompute的SQL語句，需要同時執行關聯（JOIN）操作和對資料過濾時，您需要特別關注對於不同的JOIN類型，過濾條件所在位置不同，因為計算順序的差異可能會導致查詢結果不同。本文以一個具體樣本介紹不同JOIN操作的過濾條件在不同位置時，對查詢結果的影響。

    概述

    JOIN類型如下所示。

    類型

    說明

    INNER JOIN

    輸出符合關聯條件的資料。

    LEFT JOIN

    輸出左表的所有記錄，以及右表中符合關聯條件的資料。右表中不符合關聯條件的行，輸出NULL。

    RIGHT JOIN

    輸出右表的所有記錄，以及左表中符合關聯條件的資料。左表中不符合關聯條件的行，輸出NULL。

    FULL JOIN

    輸出左表和右表的所有記錄，對於不符合關聯條件的資料，未關聯的另一側輸出NULL。

    LEFT SEMI JOIN

    對於左表中的一條資料，如果右表存在符合關聯條件的行，則輸出左表。

    LEFT ANTI JOIN

    對於左表中的一條資料，如果右表中不存在符合關聯條件的資料，則輸出左表。

    SQL語句中，同時存在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. 子查詢中的WHERE子句（即{subquery_where_condition}）。

    2. JOIN子句中的關聯條件（即{on_condition}）。

    3. JOIN結果集中的WHERE子句（即{where_condition}）。

    因此，對於不同的JOIN類型，過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}中時，查詢結果可能一致，也可能不一致。詳情請參見情境說明。

    樣本資料

    • 表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和表B的笛卡爾乘積

      計算笛卡爾乘積SQL如下：

      SET odps.sql.allow.cartesian=true;
SELECT * FROM A,B;

      返回結果如下：

      +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 1    | 20180101 |
| 2    | 20180102 | 1    | 20180101 |
| 1    | 20180101 | 3    | 20180101 |
| 2    | 20180101 | 3    | 20180101 |
| 2    | 20180102 | 3    | 20180101 |
| 1    | 20180101 | 2    | 20180102 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+

    情境說明

    • INNER JOIN

      INNER JOIN對左右表執行笛卡爾乘積，然後輸出滿足ON運算式的行。

      結論：過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}中時，查詢結果是一致的。

      • 情況1：過濾條件在子查詢{subquery_where_condition}中。 

        SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

        結果如下。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

      • 情況2：過濾條件在JOIN的關聯條件{on_condition}中。 

        SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

        笛卡爾積結果為9條，滿足關聯條件的結果只有1條，如下。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 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條，滿足關聯條件的結果有3條，如下。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+

        對上述滿足關聯條件的結果執行JOIN結果集中的過濾條件A.ds='20180101' and B.ds='20180101'，結果只有1條，如下。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

    • LEFT JOIN

      LEFT JOIN對左右表執行笛卡爾乘積，輸出滿足ON運算式的行。對於左表中不滿足ON運算式的行，輸出左表，右表輸出NULL。

      結論：過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}中時，查詢結果不一致。

      • 左表的過濾條件在{subquery_where_condition}和{where_condition}中時，查詢結果是一致的。

      • 右表的過濾條件在{subquery_where_condition}和{on_condition}中時，查詢結果是一致的。

      • 情況1：過濾條件在子查詢{subquery_where_condition}中。 

        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;

        結果如下。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | NULL | NULL |
+------+------+------+------+

      • 情況2：過濾條件在JOIN的關聯條件{on_condition}中。 

        SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

        笛卡爾積的結果有9條，滿足關聯條件的結果只有1條。左表輸出剩餘不滿足關聯條件的兩條記錄，右表輸出NULL。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | NULL | NULL |
| 2    | 20180102 | NULL | NULL |
+------+------+------+------+

      • 情況3：過濾條件在JOIN結果集的WHERE子句中。 

        SELECT A.*, B.*
FROM A LEFT JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

        笛卡爾積的結果為9條，滿足ON條件的結果有3條。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
+------+------+------+------+

        對上述結果執行JOIN結果集中的過濾條件A.ds='20180101' and B.ds='20180101'，結果只有1條。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

    • RIGHT JOIN

      RIGHT JOIN和LEFT JOIN是類似的，只是左右表的區別。

      • 過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}時，查詢結果不一致。

      • 右表的過濾條件，在{subquery_where_condition}和{where_condition}中時，查詢結果一致。

      • 左表的過濾條件，放在{subquery_where_condition}和{on_condition}中時，查詢結果一致。

    • FULL JOIN

      FULL JOIN對左右表執行笛卡爾乘積，然後輸出滿足關聯條件的行。對於左右表中不滿足關聯條件的行，輸出有資料表的行，無資料的表輸出NULL。

      結論：過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}時，查詢結果不一致。

      • 情況1：過濾條件在子查詢{subquery_where_condition}中。 

        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;

        結果如下。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | NULL | NULL |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+

      • 情況2：過濾條件在JOIN的關聯條件{on_condition}中。 

        SELECT A.*, B.*
FROM A FULL JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

        笛卡爾積的結果有9條，滿足關聯條件的結果只有1條。對於左表不滿足關聯條件的兩條記錄輸出左表資料，右表輸出NULL。對於右表不滿足關聯條件的兩條記錄輸出右表資料，左表輸出NULL。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| NULL | NULL | 2    | 20180102 |
| 2    | 20180101 | NULL | NULL |
| 2    | 20180102 | NULL | NULL |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+

      • 情況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條，滿足關聯條件的結果有3條。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
| 1    | 20180101 | 1    | 20180101 |

        對於不滿足關聯條件的表輸出資料，另一表輸出NULL。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 2    | 20180101 | 2    | 20180102 |
| 2    | 20180102 | 2    | 20180102 |
| 1    | 20180101 | 1    | 20180101 |
| NULL | NULL | 3    | 20180101 |
+------+------+------+------+

        對上述結果執行JOIN結果集中的過濾條件A.ds='20180101' and B.ds='20180101'，結果只有1條。 

        +------+------+------+------+
| key  | ds   | key2 | ds2  |
+------+------+------+------+
| 1    | 20180101 | 1    | 20180101 |
+------+------+------+------+

    • LEFT SEMI JOIN

      LEFT SEMI JOIN將左表的每一條記錄，和右表進行匹配。如果匹配成功，則輸出左表。如果匹配不成功，則跳過。由於只輸出左表，所以JOIN後的WHERE條件中不涉及右表。

      結論：過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}中時，查詢結果是一致的。

      • 情況1：過濾條件在子查詢{subquery_where_condition}中。 

        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;

        結果如下。 

        +------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

      • 情況2：過濾條件在JOIN的關聯條件{on_condition}中。 

        SELECT A.*
FROM A LEFT SEMI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

        結果如下。 

        +------+------+
| key  | 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';

        符合關聯條件的結果如下。 

        +------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

        對上述結果執行JOIN結果集中的過濾條件A.ds='20180101'，結果如下。 

        +------+------+
| key  | ds   |
+------+------+
| 1    | 20180101 |
+------+------+

    • LEFT ANTI JOIN

      LEFT ANTI JOIN將左表的每一條記錄，和右表進行匹配。如果右表中的記錄不匹配，則輸出左表。由於只輸出左表，所以JOIN後的WHERE條件中不能涉及右表。LEFT ANTI JOIN常常用來實現NOT EXISTS語義。

      結論：過濾條件在{subquery_where_condition}、{on_condition}和{where_condition}中時，查詢結果不一致。

      • 左表的過濾條件在{subquery_where_condition}和{where_condition}中時，查詢結果是一致的。

      • 右表的過濾條件在{subquery_where_condition}和{on_condition}中時，查詢結果是一致的。

      • 情況1：過濾條件在子查詢{subquery_where_condition}中。 

        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;

        結果如下。

        +------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
+------+------+

      • 情況2：過濾條件在JOIN的關聯條件{on_condition}中。 

        SELECT A.*
FROM A LEFT ANTI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

        結果如下。

        +------+------+
| key  | 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';

        左表中符合關聯條件的資料如下。

        +------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
| 2    | 20180102 |
+------+------+

        對上述結果執行JOIN結果集中的過濾條件A.ds='20180101'，結果如下。 

        +------+------+
| key  | ds   |
+------+------+
| 2    | 20180101 |
+------+------+

    注意事項

    • 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}中。

    相關文檔

    • MaxCompute支援的基礎JOIN操作，請參見JOIN、SEMI JOIN（半串連）。

    • 大表JOIN小表情境顯式指定mapjoinHint提示以提升查詢效能，請參見MAPJOIN HINT。

    • 大表Join中表情境使用DISTRIBUTED MAPJOIN以提升查詢效能，請參見DISTRIBUTED MAPJOIN。

    • 兩張表JOIN存在熱點，導致出現長尾問題時，SkewJoin Hint可以擷取兩張表的熱點Key，以加快JOIN的執行速度，請參見SKEWJOIN HINT。