如何使用Join功能 - Tablestore

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操作的具體執行演算法上，Table Store提供了INDEX JOIN和HASH JOIN兩種Join演算法。Table Store預設的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演算法的使用條件是在串連的列上，對於左表無要求，對於右表需要滿足索引的使用條件。
- 對於主鍵和二級索引，需要滿足最左匹配條件.
- 對於多元索引，需要全部命中多元索引，滿足多元索引下推的條件。
當右表使用了比較多的過濾條件時，在無索引情況下的讀取效能會下降。
使用Table StoreSQL的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 |
+----------+-------------+------------+--------------+-------------+---------------+----------------+