全部產品
Search

搜尋本產品

    MaxCompute：JOIN

    文件中心

    MaxCompute：JOIN

    更新時間：Dec 10, 2024

    MaxCompute支援通過JOIN操作串連表並返回符合串連條件和查詢條件的資料。本文為您介紹左串連、右串連、全串連、內串連、自然串連、隱式串連和多路串連的使用方法。

    功能介紹

    MaxCompute支援如下JOIN操作：

    • 左串連（LEFT OUTER JOIN）

      可簡寫為LEFT JOIN。返回左表中的所有記錄，即使右表中沒有與之匹配的記錄。

      說明

      通常，JOIN操作的左邊為大表，右表為小表，如果右表值不唯一，建議不要連續使用過多LEFT JOIN，以免在JOIN過程中產生資料膨脹，導致作業停滯。

    • 右串連（RIGHT OUTER JOIN）

      可簡寫為RIGHT JOIN。返回右表中的所有記錄，即使左表中沒有與之匹配的記錄。

    • 全串連（FULL OUTER JOIN）

      可簡寫為FULL JOIN。返回左右表中的所有記錄。

    • 內串連（INNER JOIN）

      關鍵字INNER可以省略。左右表中至少存在一個匹配行時，INNER JOIN返回資料行。

    • 自然串連（NATURAL JOIN）

      參與JOIN的兩張表根據欄位名稱自動決定串連欄位。支援OUTER NATURAL JOIN，支援使用USING子句執行JOIN，輸出欄位中公用欄位只出現一次。

    • 隱式串連

      即不指定JOIN關鍵字執行串連。

    • 多路串連

      多路JOIN串連。支援通過括弧指定JOIN的優先順序，括弧內的JOIN優先順序較高。

    說明

    • 如果SQL語句中包含WHERE過濾條件，且JOIN在WHERE條件之前，先進行JOIN操作，然後對JOIN的結果執行WHERE條件過濾，擷取的結果是兩個表的交集，而不是全表。

    • odps.task.sql.outerjoin.ppd參數可以控制OUTER JOIN ON條件中的非關聯過濾條件是否會下推到JOIN的輸入中，該參數支援Project或Session層級設定。

      • 當參數值為FALSE時，會把寫在ON中的非關聯條件當作關聯對應子查詢中的WHERE條件，這是一個非標準的行為，建議將非關聯條件移到WHERE子句中。

      • 當參數值為FALSE時，如下兩個SQL語句等價；當參數值為TRUE時，兩者不等價。

      SELECT A.*, B.* FROM A LEFT JOIN B ON A.c1 = B.c1 and A.c2='xxx';

SELECT A.*, B.* FROM (SELECT * FROM A WHERE c2='xxx') A LEFT JOIN B ON A.c1 = B.c1;

    注意事項

    使用JOIN時，會在計算中自動加入JOIN的key is not null的過濾條件，去除關聯鍵為NULL的值所在行。

    使用限制

    JOIN操作的使用限制如下：

    • MaxCompute不支援CROSS JOIN，即無ON條件的串連。

    • 只允許出現AND串連的等值條件。您可以通過MAPJOIN操作使用不等值串連或OR串連多個條件，詳情請參見MAPJOIN。

    命令格式

    <table_reference> JOIN <table_factor> [<join_condition>]
| <table_reference> {LEFT OUTER|RIGHT OUTER|FULL OUTER|INNER|NATURAL} JOIN <table_reference> <join_condition>

    • table_reference：必填。待執行JOIN操作的左表查詢語句。格式為table_name [alias] | table_query [alias] |... 。

    • table_factor：必填。待執行JOIN操作的右表或表查詢語句。格式為table_name [alias] | table_subquery [alias] |... 。

    • join_condition：可選。JOIN串連條件，是一個或多個等式運算式組合。格式為on equality_expression [and equality_expression]...，equality_expression為等式運算式。

    說明

    如果分區裁剪條件置於WHERE子句中，分區裁剪會生效；如果置於ON子句中，從表的分區裁剪會生效，主表的分區剪裁不會生效即會全表掃描。詳情請參見分區剪裁合理性評估。

    樣本資料

    為便於理解，本文為您提供來源資料，基於來源資料提供相關樣本。建立表sale_detail和sale_detail_jt，並添加資料，命令樣本如下：

    --建立分區表sale_detail和sale_detail_jt。
CREATE TABLE if NOT EXISTS sale_detail
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

CREATE TABLE if NOT EXISTS sale_detail_jt
(
shop_name     STRING,
customer_id   STRING,
total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

--向源表增加分區。
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');
ALTER TABLE sale_detail_jt ADD PARTITION (sale_date='2013', region='china');

--向源表追加資料。
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
INSERT INTO sale_detail_jt PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s5','c2',100.2);

--查詢表sale_detail和sale_detail_jt中的資料，命令樣本如下：
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;
--返回結果
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_jt;
-- 返回結果
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s5         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

--建立做關聯的表。
SET odps.sql.allow.fullscan=true;
CREATE TABLE shop AS SELECT shop_name, customer_id, total_price FROM sale_detail;

    使用樣本

    下述樣本均基於樣本資料為您展示JOIN的相關用法。

    • 樣本1：左串連。命令樣本如下：

      --分區表需要開啟全表掃描功能，否則join操作會執行失敗。
SET odps.sql.allow.fullscan=true;
--由於表sale_detail_jt及sale_detail中都有shop_name列，因此需要在select子句中使用別名進行區分。
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       LEFT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

      返回結果如下：

      +------------+------------+
| ashop      | bshop      |
+------------+------------+
| s2         | s2         |
| s1         | s1         |
| s5         | NULL       |
+------------+------------+

    • 樣本2：右串連。命令樣本如下：

      --分區表需要開啟全表掃描功能，否則join操作會執行失敗。
SET odps.sql.allow.fullscan=true;
--由於表sale_detail_jt及sale_detail中都有shop_name列，因此需要在select子句中使用別名進行區分。
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       RIGHT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

      返回結果如下：

      +------------+------------+
| ashop      | bshop      |
+------------+------------+
| s1         | s1         |
| s2         | s2         |
| NULL       | s3         |
| NULL       | null       |
| NULL       | s6         |
| NULL       | s7         |
+------------+------------+

    • 樣本3：全串連。命令樣本如下：

      --分區表需要開啟全表掃描功能，否則join操作會執行失敗。
SET odps.sql.allow.fullscan=true;
--由於表sale_detail_jt及sale_detail中都有shop_name列，因此需要在select子句中使用別名進行區分。
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       FULL OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

      返回結果如下：

      +------------+------------+
| ashop      | bshop      |
+------------+------------+
| NULL       | s3         |
| NULL       | s6         |
| s2         | s2         |
| NULL       | null       |
| NULL       | s7         |
| s1         | s1         |
| s5         | NULL       |
+------------+------------+

    • 樣本4：內串連。命令樣本如下：

      --分區表需要開啟全表掃描功能，否則join操作會執行失敗。
SET odps.sql.allow.fullscan=true;
--由於表sale_detail_jt及sale_detail中都有shop_name列，因此需要在select子句中使用別名進行區分。
SELECT a.shop_name AS ashop, b.shop_name AS bshop FROM sale_detail_jt a 
       INNER JOIN sale_detail b ON a.shop_name=b.shop_name;

      返回結果如下：

      +------------+------------+
| ashop      | bshop      |
+------------+------------+
| s2         | s2         |
| s1         | s1         |
+------------+------------+

    • 樣本5：自然串連。命令樣本如下：

      --分區表需要開啟全表掃描功能，否則join操作會執行失敗。
SET odps.sql.allow.fullscan=true;
--自然串連。
SELECT * FROM sale_detail_jt NATURAL JOIN sale_detail;
--等效於如下語句。
SELECT sale_detail_jt.shop_name AS shop_name, sale_detail_jt.customer_id AS customer_id, sale_detail_jt.total_price AS total_price, sale_detail_jt.sale_date as sale_date, sale_detail_jt.region as region from sale_detail_jt 
INNER JOIN sale_detail 
ON sale_detail_jt.shop_name=sale_detail.shop_name AND sale_detail_jt.customer_id=sale_detail.customer_id and sale_detail_jt.total_price=sale_detail.total_price AND sale_detail_jt.sale_date=sale_detail.sale_date AND sale_detail_jt.region=sale_detail.region;

      返回結果如下：

      +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

    • 樣本6：隱式串連。命令樣本如下：

      --分區表需要開啟全表掃描功能，否則join操作會執行失敗。
SET odps.sql.allow.fullscan=true;
--隱式串連。
SELECT * FROM sale_detail_jt, sale_detail WHERE sale_detail_jt.shop_name = sale_detail.shop_name;
--等效於如下語句。
SELECT * FROM sale_detail_jt JOIN sale_detail ON sale_detail_jt.shop_name = sale_detail.shop_name;

      返回結果如下：

      +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     | shop_name2 | customer_id2 | total_price2 | sale_date2 | region2    |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+
| s2         | c2          | 100.2       | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+

    • 樣本7：多路串連，不指定優先順序。命令樣本如下：

      --分區表需要開啟全表掃描功能，否則join操作會執行失敗。
SET odps.sql.allow.fullscan=true;
--由於表sale_detail_jt及sale_detail中都有shop_name列，因此需要在select子句中使用別名進行區分。
SELECT a.* FROM sale_detail_jt a FULL OUTER JOIN sale_detail b ON a.shop_name=b.shop_name
        FULL OUTER JOIN sale_detail c ON a.shop_name=c.shop_name;

      返回結果如下：

      +------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s5         | c2          | 100.2       | 2013       | china      |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| s1         | c1          | 100.1       | 2013       | china      |
| NULL       | NULL        | NULL        | NULL       | NULL       |
| s2         | c2          | 100.2       | 2013       | china      |
| NULL       | NULL        | NULL        | NULL       | NULL       |
+------------+-------------+-------------+------------+------------+

    • 樣本7：多路串連，通過括弧指定優先順序。命令樣本如下：

      --分區表需要開啟全表掃描功能，否則join操作會執行失敗。
SET odps.sql.allow.fullscan=true;
--多路串連，通過括弧指定優先順序。
SELECT * FROM shop JOIN (sale_detail_jt JOIN sale_detail ON sale_detail_jt.shop_name = sale_detail.shop_name) ON shop.shop_name=sale_detail_jt.shop_name;

      返回結果如下：

      +------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     | shop_name2 | customer_id2 | total_price2 | sale_date2 | region2    | shop_name3 | customer_id3 | total_price3 | sale_date3 | region3    |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+
| s2         | c2          | 100.2       | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      | s2         | c2           | 100.2        | 2013       | china      |
| s1         | c1          | 100.1       | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      | s1         | c1           | 100.1        | 2013       | china      |
+------------+-------------+-------------+------------+------------+------------+--------------+--------------+------------+------------+------------+--------------+--------------+------------+------------+

    • 樣本8：join與where相結合，查詢兩表中region為china且shop_name一致的記錄數，保留sale_detail表的全部記錄。命令樣本如下：

      --分區表需要開啟全表掃描功能，否則join操作會執行失敗。
SET odps.sql.allow.fullscan=true;
--執行SQL語句。
SELECT a.shop_name
    ,a.customer_id
    ,a.total_price
        ,b.total_price 
FROM  (SELECT * FROM sale_detail WHERE region = "china") a 
LEFT JOIN (SELECT * FROM sale_detail_jt WHERE region = "china") b 
ON   a.shop_name = b.shop_name;

      返回結果如下：

      +------------+-------------+-------------+--------------+
| shop_name  | customer_id | total_price | total_price2 |
+------------+-------------+-------------+--------------+
| s1         | c1          | 100.1       | 100.1        |
| s2         | c2          | 100.2       | 100.2        |
| s3         | c3          | 100.3       | NULL         |
+------------+-------------+-------------+--------------+

      錯誤命令樣本如下：

      SELECT a.shop_name
    ,a.customer_id
    ,a.total_price
        ,b.total_price 
FROM  sale_detail a 
LEFT JOIN sale_detail_jt b 
ON   a.shop_name = b.shop_name 
WHERE  a.region = "china" AND b.region = "china";

      返回結果如下：

      +------------+-------------+-------------+--------------+
| shop_name  | customer_id | total_price | total_price2 |
+------------+-------------+-------------+--------------+
| s1         | c1          | 100.1       | 100.1        |
| s2         | c2          | 100.2       | 100.2        |
+------------+-------------+-------------+--------------+

      從返回結果可看到，擷取的結果是兩個表的交集，非sale_detail表的全部記錄。