如何使用Join功能 - 表格存储

Join功能允许将两个表或多个表进行连接，并返回符合连接条件和查询条件的数据。

注意事项

本文中提到的左表和右表通过表在JOIN关键词的左边或者右边进行区分。

如下SQL语句中JOIN关键词左边的表table_1即为左表，右边的表table_2即为右表。

SELECT * FROM table_1 JOIN table_2 on ......;

语法

table_references join_type table_references [ ON join_condition | USING ( join_column [, ...] ) ]

table_references : {
 table_name [ [ AS ] alias_name ]
 | select_statement
}

join_type : {
 [ INNER ] JOIN
 | LEFT [ OUTER ] JOIN
 | RIGHT [ OUTER ] JOIN
 | CROSS JOIN
}

参数

参数	是否必选	说明
table_references	是	目标表信息，可以为表名或者SELECT语句。左表和右表通过表在JOIN关键词的左边或者右边进行区分。
join_type	是	Join类型。取值范围如下： INNER JOIN（默认）：只返回满足join条件的行。 LEFT OUTER JOIN：返回左表中的所有行，并根据连接条件连接右表的数据。对于在右表中没有匹配数据的行，右表数据填充NULL。 RIGHT OUTER JOIN：和LEFT OUTER JOIN类似，返回右表中的所有行，并根据连接条件连接左表的数据。对于在左表中没有匹配数据的行，左表数据填充NULL。 CROSS JOIN：返回两表的笛卡尔积，左表中的每行数据均会与右表中的所有数据进行连接。
join_condition	是	指定要进行连接的列。如下SQL语句中即为对table_1表的col_A列和table_2表的col_B 列相等的行进行连接。 `SELECT * FROM table_1 JOIN table_2 on table_1.col_A = table_2.col_B;` 当两个表在join条件上的列名相同时，可以使用`USING ( join_column [, ...] )`来简化SQL语句。如下SQL语句即可使用USING进行简化。 `SELECT * FROM table_1 JOIN table_2 on table_1.col_A = table_2.col_A and table_1.col_B = table_2.col_B;` 简化后的SQL语句如下： `SELECT * FROM table_1 JOIN table_2 USING (col_A, col_B);`

Join算法

在Join操作的具体执行算法上，表格存储提供了INDEX JOIN和HASH JOIN两种Join算法。表格存储默认的Join算法为INDEX JOIN，当右表进行Join的列不满足INDEX JOIN算法的使用条件时，系统会使用HASH JOIN算法。

INDEX JOIN（默认）：从左表读取数据，根据左表的数据，利用右表的索引或主键，读取右表匹配的行进行连接。更多信息，请参见INDEX JOIN。
HASH JOIN：从左表读取数据建立哈希表，再读取右表探测哈希表判断是否有匹配的行并进行连接。更多信息，请参见HASH JOIN。

INDEX JOIN

使用INDEX JOIN算法时，在右表进行Join的列上必须满足对应的索引选择条件，对左表无要求。

对于数据表主键或二级索引，右表进行Join的列必须满足最左匹配条件。
对于多元索引，右表进行Join的列必须全部命中某一个多元索引。如果右表带有其他过滤条件，则必须能全部下推到Join列所命中的多元索引。

当需要连接的行数较少、左表的数据量较小或者左表本身数据量较大但在进行过滤后的数据量较小，并且右表同时满足使用INDEX JOIN算法的使用条件时，使用INDEX JOIN算法一般会有更好的性能。在INDEX JOIN算法的场景下，当使用内连接并且进行Join的两张表均满足INDEX JOIN算法的使用条件时，建议将数据量更小的表或者加上过滤条件后数据量更小的表设置为左表。

HASH JOIN

HASH JOIN算法无场景限制。当不满足INDEX JOIN算法的使用条件，无法使用INDEX JOIN算法时，系统会选择HASH JOIN算法。

当需要连接的行数很多，使用HASH JOIN算法一般会有更好的性能。

在HASH JOIN算法的场景下，当使用内连接时，建议将数据量更小的表或者加上过滤条件后数据量更小的表设置为左表。

使用建议

INDEX JOIN算法的使用条件是在连接的列上，对于左表无要求，对于右表需要满足索引的使用条件。
- 对于主键和二级索引，需要满足最左匹配条件.
- 对于多元索引，需要全部命中多元索引，满足多元索引下推的条件。
当右表使用了比较多的过滤条件时，在无索引情况下的读取性能会下降。
使用表格存储SQL的Join功能时，如果Join类型为INNER JOIN，JOIN关键词左边的表为外表，JOIN关键词右边的表为内表。当使用INNER JOIN时，建议将数据行数较少的表或者使用过滤条件后输出数据行数较少的表当作外表（即在JOIN关键词左边的表）。
当需要连接的行数较少、进行Join的外表行数比较少或者使用过滤条件后输出数据行数较少时，使用INDEX JOIN算法的性能会更好。当表的规模较大或者需要连接的行数较多时，使用INDEX JOIN算法的性能会有所下降，HASH JOIN算法的性能会更好。
当使用Join的SQL语句中某张表使用了比较多的过滤条件时，在无索引的情况下读取性能会有所下降，此时使用索引加速数据的读取可能会加速整体的执行速度。

示例

假设有orders和customers两张表，数据如下：

SELECT * FROM orders;
+----------+-------------+------------+--------------+
| order_id | customer_id | order_date | order_amount |
+----------+-------------+------------+--------------+
|     1001 |           1 | 2023-01-01 |           50 |
|     1002 |           2 | 2023-01-02 |           80 |
|     1003 |           3 | 2023-01-03 |          180 |
|     1004 |           4 | 2023-01-04 |          220 |
|     1005 |           6 | 2023-01-05 |          250 |
+----------+-------------+------------+--------------+

SELECT * FROM customers;
+-------------+---------------+----------------+
| customer_id | customer_name | customer_phone |
+-------------+---------------+----------------+
|           1 |         Alice |    11111111111 |
|           2 |           Bob |    22222222222 |
|           3 |         Carol |    33333333333 |
|           4 |         David |    44444444444 |
|           5 |           Eve |    55555555555 |
+-------------+---------------+----------------+

示例一：INNER JOIN

SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;

等价于使用USING的如下SQL语句：

SELECT * FROM orders JOIN customers USING(customer_id);

返回结果示例如下：

+----------+-------------+------------+--------------+-------------+---------------+----------------+
| order_id | customer_id | order_date | order_amount | customer_id | customer_name | customer_phone |
+----------+-------------+------------+--------------+-------------+---------------+----------------+
|     1001 |           1 | 2023-01-01 |           50 |           1 |         Alice |    11111111111 |
|     1002 |           2 | 2023-01-02 |           80 |           2 |           Bob |    22222222222 |
|     1003 |           3 | 2023-01-03 |          180 |           3 |         Carol |    33333333333 |
|     1004 |           4 | 2023-01-04 |          220 |           4 |         David |    44444444444 |
+----------+-------------+------------+--------------+-------------+---------------+----------------+

示例二：LEFT JOIN

SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id;

等价于使用USING的如下SQL语句：

SELECT * FROM orders LEFT JOIN customers USING(customer_id);

返回结果示例如下：

+----------+-------------+------------+--------------+-------------+---------------+----------------+
| order_id | customer_id | order_date | order_amount | customer_id | customer_name | customer_phone |
+----------+-------------+------------+--------------+-------------+---------------+----------------+
|     1001 |           1 | 2023-01-01 |           50 |           1 |         Alice |    11111111111 |
|     1002 |           2 | 2023-01-02 |           80 |           2 |           Bob |    22222222222 |
|     1003 |           3 | 2023-01-03 |          180 |           3 |         Carol |    33333333333 |
|     1004 |           4 | 2023-01-04 |          220 |           4 |         David |    44444444444 |
|     1005 |           6 | 2023-01-05 |          250 |        NULL |          NULL |           NULL |
+----------+-------------+------------+--------------+-------------+---------------+----------------+

示例三：CROSS JOIN

SELECT * FROM orders CROSS JOIN customers;

返回结果示例如下：

+----------+-------------+------------+--------------+-------------+---------------+----------------+
| order_id | customer_id | order_date | order_amount | customer_id | customer_name | customer_phone |
+----------+-------------+------------+--------------+-------------+---------------+----------------+
|     1001 |           1 | 2023-01-01 |           50 |           1 |         Alice |    11111111111 |
|     1002 |           2 | 2023-01-02 |           80 |           1 |         Alice |    11111111111 |
|     1003 |           3 | 2023-01-03 |          180 |           1 |         Alice |    11111111111 |
|     1004 |           4 | 2023-01-04 |          220 |           1 |         Alice |    11111111111 |
|     1005 |           6 | 2023-01-05 |          250 |           1 |         Alice |    11111111111 |
|     1001 |           1 | 2023-01-01 |           50 |           2 |           Bob |    22222222222 |
|     1002 |           2 | 2023-01-02 |           80 |           2 |           Bob |    22222222222 |
|     1003 |           3 | 2023-01-03 |          180 |           2 |           Bob |    22222222222 |
|     1004 |           4 | 2023-01-04 |          220 |           2 |           Bob |    22222222222 |
|     1005 |           6 | 2023-01-05 |          250 |           2 |           Bob |    22222222222 |
|     1001 |           1 | 2023-01-01 |           50 |           3 |         Carol |    33333333333 |
|     1002 |           2 | 2023-01-02 |           80 |           3 |         Carol |    33333333333 |
|     1003 |           3 | 2023-01-03 |          180 |           3 |         Carol |    33333333333 |
|     1004 |           4 | 2023-01-04 |          220 |           3 |         Carol |    33333333333 |
|     1005 |           6 | 2023-01-05 |          250 |           3 |         Carol |    33333333333 |
|     1001 |           1 | 2023-01-01 |           50 |           4 |         David |    44444444444 |
|     1002 |           2 | 2023-01-02 |           80 |           4 |         David |    44444444444 |
|     1003 |           3 | 2023-01-03 |          180 |           4 |         David |    44444444444 |
|     1004 |           4 | 2023-01-04 |          220 |           4 |         David |    44444444444 |
|     1005 |           6 | 2023-01-05 |          250 |           4 |         David |    44444444444 |
|     1001 |           1 | 2023-01-01 |           50 |           5 |           Eve |    55555555555 |
|     1002 |           2 | 2023-01-02 |           80 |           5 |           Eve |    55555555555 |
|     1003 |           3 | 2023-01-03 |          180 |           5 |           Eve |    55555555555 |
|     1004 |           4 | 2023-01-04 |          220 |           5 |           Eve |    55555555555 |
|     1005 |           6 | 2023-01-05 |          250 |           5 |           Eve |    55555555555 |
+----------+-------------+------------+--------------+-------------+---------------+----------------+