JOIN - 云原生大数据计算服务 MaxCompute

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表的全部记录。