全部產品
Search

搜尋本產品

    MaxCompute：JOIN

    文件中心

    MaxCompute：JOIN

    更新時間：Feb 28, 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中的資料，命令樣本如下：
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   |
+------------+-------------+-------------+------------+------------+
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;

    使用樣本

    • 樣本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      |
+------------+------------+
| NULL       | s3         |
| NULL       | s6         |
| NULL       | null       |
| s2         | s2         |
| NULL       | s7         |
| s1         | s1         |
+------------+------------+

    • 樣本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表的全部記錄。