JOIN - OceanBase 数据库 V4.3.5 | OceanBase 文档中心

JOIN 算子用于将两张表的数据，按照特定的条件进行联接。

说明

联接（JOIN）的类型主要包括内联接（Inner Join）、外联接（Outer Join）和半联接（Semi/Anti Join）三种。

JOIN 算子类型

OceanBase 数据库支持的 JOIN 算子主要有：NESTED-LOOP JOIN、MERGE JOIN 和 HASH JOIN。

算子类型	特性说明	核心原理	适用场景
NESTED-LOOP JOIN	外层循环遍历左表，内层循环针对每一行左表数据去扫描右表。	扫描一个表（左表），每读到表中的一条记录，就去另一张表（右表）中查找满足联接条件的行。	右表有高效索引。左表结果集极小。非等值联接。
MERGE JOIN	排序后合并有序序列。	先按照联接字段对两个输入表进行排序，然后像合并两个有序队列一样，同步顺序扫描两个已排序的表，找出匹配的行进行合并。	输入数据已有序。结果需有序输出。等值联接。
HASH JOIN	构建哈希表并探测。	它选择两个表中相对较小的一个作为构建表，根据联接条件计算其每一行数据的哈希值，并在内存中构建一个哈希表。然后扫描较大的探测表，同样计算其每一行数据的哈希值，到哈希表中查找匹配的构建表行，完成联接。	大数据量等值联接。缺乏有效索引。

NESTED-LOOP JOIN

NESTED-LOOP JOIN（简称 NLJ）表示嵌套循环联接。

可通过 USE_NL Hint 强制优化器使用 NLJ。USE_NL Hint 指定的表作为联接的左表时，使用嵌套循环联接（NL-JOIN）算法。有关 USE_NL Hint 的详细介绍信息，参见 Join Operation Hint 下 USE_NL Hint 章节。

NESTED-LOOP JOIN 示例

创建表 tbl1。

obclient> CREATE TABLE tbl1(col1 INT, col2 INT);

创建表 tbl2，定义列 col1 为主键。

obclient> CREATE TABLE tbl2(col1 INT PRIMARY KEY, col2 INT);

Q1：指定 USE_NL Hint 使用 NLJ 的方式，查询 tbl1.col2 = tbl2.col2 的行，并计算 tbl1.col2 + tbl2.col2，同时查看该查询的执行计划。

obclient> EXPLAIN SELECT /*+USE_NL(tbl1, tbl2)*/ tbl1.col2 + tbl2.col2
    FROM tbl1, tbl2
    WHERE tbl1.col2 = tbl2.col2;

返回结果如下：

+------------------------------------------------------------------------+
| Query Plan                                                             |
+------------------------------------------------------------------------+
| ===================================================                    |
| |ID|OPERATOR           |NAME|EST.ROWS|EST.TIME(us)|                    |
| ---------------------------------------------------                    |
| |0 |NESTED-LOOP JOIN   |    |1       |5           |                    |
| |1 |├─TABLE FULL SCAN  |TBL1|1       |3           |                    |
| |2 |└─MATERIAL         |    |1       |3           |                    |
| |3 |  └─TABLE FULL SCAN|TBL2|1       |3           |                    |
| ===================================================                    |
| Outputs & filters:                                                     |
| -------------------------------------                                  |
|   0 - output([TBL1.COL2 + TBL2.COL2]), filter(nil), rowset=16          |
|       conds([TBL1.COL2 = TBL2.COL2]), nl_params_(nil), use_batch=false |
|   1 - output([TBL1.COL2]), filter(nil), rowset=16                      |
|       access([TBL1.COL2]), partitions(p0)                              |
|       is_index_back=false, is_global_index=false,                      |
|       range_key([TBL1.__pk_increment]), range(MIN ; MAX)always true    |
|   2 - output([TBL2.COL2]), filter(nil), rowset=16                      |
|   3 - output([TBL2.COL2]), filter(nil), rowset=16                      |
|       access([TBL2.COL2]), partitions(p0)                              |
|       is_index_back=false, is_global_index=false,                      |
|       range_key([TBL2.COL1]), range(MIN ; MAX)always true              |
+------------------------------------------------------------------------+
21 rows in set

Q2：指定 USE_NL Hint 使用 NLJ 的方式，查询 tbl1.col1 = tbl2.col1 的行，并计算 tbl1.col2 + tbl2.col2，同时查看该查询的执行计划。

obclient> EXPLAIN SELECT /*+USE_NL(tbl1, tbl2)*/ tbl1.col2 + tbl2.col2
    FROM tbl1, tbl2
    WHERE tbl1.col1 = tbl2.col1;

返回结果如下：

+---------------------------------------------------------------------+
| Query Plan                                                          |
+---------------------------------------------------------------------+
| =================================================                   |
| |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                   |
| -------------------------------------------------                   |
| |0 |NESTED-LOOP JOIN |    |1       |19          |                   |
| |1 |├─TABLE FULL SCAN|TBL1|1       |3           |                   |
| |2 |└─TABLE GET      |TBL2|1       |16          |                   |
| =================================================                   |
| Outputs & filters:                                                  |
| -------------------------------------                               |
|   0 - output([TBL1.COL2 + TBL2.COL2]), filter(nil), rowset=16       |
|       conds(nil), nl_params_([TBL1.COL1(:0)]), use_batch=true       |
|   1 - output([TBL1.COL1], [TBL1.COL2]), filter(nil), rowset=16      |
|       access([TBL1.COL1], [TBL1.COL2]), partitions(p0)              |
|       is_index_back=false, is_global_index=false,                   |
|       range_key([TBL1.__pk_increment]), range(MIN ; MAX)always true |
|   2 - output([TBL2.COL2]), filter(nil), rowset=16                   |
|       access([GROUP_ID], [TBL2.COL2]), partitions(p0)               |
|       is_index_back=false, is_global_index=false,                   |
|       range_key([TBL2.COL1]), range(MIN ; MAX),                     |
|       range_cond([:0 = TBL2.COL1])                                  |
+---------------------------------------------------------------------+
20 rows in set

Q3：查看从 tbl1 和 tbl2 进行笛卡尔积联接后（不存在联接条件），计算 tbl1.col2 + tbl2.col2 的查询执行计划。

obclient> EXPLAIN SELECT tbl1.col2 + tbl2.col2
    FROM tbl1, tbl2;

返回结果如下：

+---------------------------------------------------------------------+
| Query Plan                                                          |
+---------------------------------------------------------------------+
| ===========================================================         |
| |ID|OPERATOR                   |NAME|EST.ROWS|EST.TIME(us)|         |
| -----------------------------------------------------------         |
| |0 |NESTED-LOOP JOIN CARTESIAN |    |1       |5           |         |
| |1 |├─TABLE FULL SCAN          |TBL1|1       |3           |         |
| |2 |└─MATERIAL                 |    |1       |3           |         |
| |3 |  └─TABLE FULL SCAN        |TBL2|1       |3           |         |
| ===========================================================         |
| Outputs & filters:                                                  |
| -------------------------------------                               |
|   0 - output([TBL1.COL2 + TBL2.COL2]), filter(nil), rowset=16       |
|       conds(nil), nl_params_(nil), use_batch=false                  |
|   1 - output([TBL1.COL2]), filter(nil), rowset=16                   |
|       access([TBL1.COL2]), partitions(p0)                           |
|       is_index_back=false, is_global_index=false,                   |
|       range_key([TBL1.__pk_increment]), range(MIN ; MAX)always true |
|   2 - output([TBL2.COL2]), filter(nil), rowset=16                   |
|   3 - output([TBL2.COL2]), filter(nil), rowset=16                   |
|       access([TBL2.COL2]), partitions(p0)                           |
|       is_index_back=false, is_global_index=false,                   |
|       range_key([TBL2.COL1]), range(MIN ; MAX)always true           |
+---------------------------------------------------------------------+
21 rows in set

在上面 Q1 和 Q2 查询中使用了 USE_NL Hint 指定使用嵌套循环联接（NL-JOIN）算法。在 Q3 查询中没有指定任何的联接条件，0 号算子展示成了一个 NESTED-LOOP JOIN CARTESIAN，逻辑上它还是一个 NLJ 算子，代表一个没有任何联接条件的 NLJ 算子。

在 Q1、Q2 和 Q3 查询返回结果中，除了 NLJ 算子（0 号算子），还展示了以下执行算子：

TABLE FULL SCAN 和 TABLE GET：这两个算子都属于 TABLE SCAN 算子，用于展示优化器选择哪个索引（或主表）来访问数据。详细信息参见 TABLE SCAN。
MATERIAL：该算子用于物化下层算子输出的数据。详细信息参见 MATERIAL。

在 Q1、Q2 和 Q3 执行计划展示中，outputs & filters 详细展示了 NESTED-LOOP JOIN 算子的具体输出信息如下：

信息名称	含义	示例说明
output	该算子最终输出的列或表达式列表。	`output([TBL1.COL2 + TBL2.COL2])` 表示此联接操作的结果是计算并输出 `TBL1.COL2` 和 `TBL2.COL2` 的和。
filter	该算子需要应用的过滤条件（谓词）。	`filter(nil)` 表示在完成联接操作后，没有需要再额外过滤的行。
rowset	表示当前算子的向量化大小。	`rowset=16` 表示当前算子的向量化大小为 16。
conds	表示进行联接操作时使用的联接条件。它决定了如何将来自两个表的行进行匹配。	在 Q1 查询中，`conds([TBL1.COL2 = TBL2.COL2])` 表示联接条件为两表的 `col2` 列相等。在 Q2 和 Q3 查询中，`conds(nil)` 并不代表没有联接条件：对于 Q2，其等值联接条件 `tbl1.col1 = tbl2.col1` 被优化器以更高效的方式处理，转移到了 `nl_params_` 中。对于 Q3，不存在联接条件。
nl_params_	`NESTED-LOOP JOIN` 算子特有参数。该参数代表从左表（驱动表）传递给右表（被探查表）的联接条件参数，即根据 `NLJ` 左表的数据产生的下推参数。	在 Q1 查询中，`nl_params_(nil)` 表示此次联接没有使用参数化的联接条件。因为联接条件 `tbl1.col2 = tbl2.col2` 所涉及的列 `col2` 在两个表上均无索引（执行计划显示为 `TABLE FULL SCAN`），优化器无法进行高效的参数化值传递，所以在联接过程中没有生成需要传递的参数。在 Q2 查询中，`nl_params_([TBL1.COL1(:0)])` 表示此次联接使用参数化的联接条件。联接条件 `tbl1.col1 = tbl2.col1` 中的 `tbl2.col1` 是表的主键。优化器将左表 `tbl1` 每一行的 `col1` 值作为参数 `:0` 动态传递给右表 `tbl2`，这使得右表能够利用主键进行高效的精确点查（`TABLE GET`）。参数 `:0` 直接用于右表的 `range_cond([:0 = TBL2.COL1])` 条件。在 Q3 查询中，`nl_params_(nil)` 表示此次联接没有使用参数化的联接条件。因为是笛卡尔积联接（`NESTED-LOOP JOIN CARTESIAN`），不存在任何等值的联接条件，所以没有需要从左表传递给右表的参数。
use_batch	表示联接操作是否启用批处理模式。	在 Q1 查询中，`use_batch=false` 表示未启用批量模式。可能的原因是，右表 `tbl2` 通过 `MATERIAL` 算子被物化（即数据被预先读取并缓存），此时逐行处理与批量处理的性能差异可能不大，因此优化器选择了更简单的逐行处理方式。在 Q2 查询中，`use_batch=true` 表示启用了批量模式。因为右表 `tbl2` 通过主键访问非常高效（`TABLE GET`），将左表 `tbl1` 的多行数据批量收集后，一次性发起对右表的多次查询，可以显著减少网络和函数调用的开销，从而大幅提升整体联接性能。在 Q3 查询中，`use_batch=false` 表示未启用批量模式。在笛卡尔积场景下，右表 `tbl2` 同样被物化（`MATERIAL`）。由于需要将左表的每一行与右表的所有行进行组合，批处理优化在此场景下的收益可能有限，甚至可能增加复杂度，因此优化器选择了不启用。

MERGE JOIN

MERGE JOIN（简称 MJ）表示排序合并联接。

可通过 USE_MERGE Hint 强制使用 MJ。USE_MERGE Hint 当此 Hint 中指定的表作为联接的右表时，使用排序合并联接算法。有关 USE_MERGE Hint 的详细介绍信息，参见 Join Operation Hint 下 USE_MERGE Hint 章节。

MERGE JOIN 示例

创建表 tbl3。

obclient> CREATE TABLE tbl3(col1 INT, col2 INT);

创建表 tbl4，定义列 col1 为主键。

obclient> CREATE TABLE tbl4(col1 INT PRIMARY KEY, col2 INT);

Q4：指定 USE_MERGE Hint 使用 MJ 的方式，查询 tbl3.col2 = tbl4.col2 且 tbl3.col1 + tbl4.col1 > 10 的行，并计算 tbl3.col2 + tbl4.col2，同时查看该查询的执行计划。

obclient> EXPLAIN SELECT /*+USE_MERGE(tbl3, tbl4)*/ tbl3.col2 + tbl4.col2
    FROM tbl3, tbl4
    WHERE tbl3.col2 = tbl4.col2
    AND tbl3.col1 + tbl4.col1 > 10;

返回结果如下：

+---------------------------------------------------------------------------------------+
| Query Plan                                                                            |
+---------------------------------------------------------------------------------------+
| ===================================================                                   |
| |ID|OPERATOR           |NAME|EST.ROWS|EST.TIME(us)|                                   |
| ---------------------------------------------------                                   |
| |0 |MERGE JOIN         |    |1       |5           |                                   |
| |1 |├─SORT             |    |1       |3           |                                   |
| |2 |│ └─TABLE FULL SCAN|TBL3|1       |3           |                                   |
| |3 |└─SORT             |    |1       |3           |                                   |
| |4 |  └─TABLE FULL SCAN|TBL4|1       |3           |                                   |
| ===================================================                                   |
| Outputs & filters:                                                                    |
| -------------------------------------                                                 |
|   0 - output([TBL3.COL2 + TBL4.COL2]), filter(nil), rowset=16                         |
|       equal_conds([TBL3.COL2 = TBL4.COL2]), other_conds([TBL3.COL1 + TBL4.COL1 > 10]) |
|       merge_directions([ASC])                                                         |
|   1 - output([TBL3.COL2], [TBL3.COL1]), filter(nil), rowset=16                        |
|       sort_keys([TBL3.COL2, ASC])                                                     |
|   2 - output([TBL3.COL2], [TBL3.COL1]), filter(nil), rowset=16                        |
|       access([TBL3.COL2], [TBL3.COL1]), partitions(p0)                                |
|       is_index_back=false, is_global_index=false,                                     |
|       range_key([TBL3.__pk_increment]), range(MIN ; MAX)always true                   |
|   3 - output([TBL4.COL2], [TBL4.COL1]), filter(nil), rowset=16                        |
|       sort_keys([TBL4.COL2, ASC])                                                     |
|   4 - output([TBL4.COL1], [TBL4.COL2]), filter(nil), rowset=16                        |
|       access([TBL4.COL1], [TBL4.COL2]), partitions(p0)                                |
|       is_index_back=false, is_global_index=false,                                     |
|       range_key([TBL4.COL1]), range(MIN ; MAX)always true                             |
+---------------------------------------------------------------------------------------+
26 rows in set

Q5：指定 USE_MERGE Hint 使用 MJ 的方式，查询 tbl3.col1 = tbl4.col1 的行，并计算 tbl3.col2 + tbl4.col2，同时查看该查询的执行计划。

obclient> EXPLAIN SELECT /*+USE_MERGE(tbl3, tbl4)*/ tbl3.col2 + tbl4.col2
    FROM tbl3, tbl4
    WHERE tbl3.col1 = tbl4.col1;

返回结果如下：

+---------------------------------------------------------------------+
| Query Plan                                                          |
+---------------------------------------------------------------------+
| ===================================================                 |
| |ID|OPERATOR           |NAME|EST.ROWS|EST.TIME(us)|                 |
| ---------------------------------------------------                 |
| |0 |MERGE JOIN         |    |1       |5           |                 |
| |1 |├─SORT             |    |1       |3           |                 |
| |2 |│ └─TABLE FULL SCAN|TBL3|1       |3           |                 |
| |3 |└─TABLE FULL SCAN  |TBL4|1       |3           |                 |
| ===================================================                 |
| Outputs & filters:                                                  |
| -------------------------------------                               |
|   0 - output([TBL3.COL2 + TBL4.COL2]), filter(nil), rowset=16       |
|       equal_conds([TBL3.COL1 = TBL4.COL1]), other_conds(nil)        |
|       merge_directions([ASC])                                       |
|   1 - output([TBL3.COL1], [TBL3.COL2]), filter(nil), rowset=16      |
|       sort_keys([TBL3.COL1, ASC])                                   |
|   2 - output([TBL3.COL1], [TBL3.COL2]), filter(nil), rowset=16      |
|       access([TBL3.COL1], [TBL3.COL2]), partitions(p0)              |
|       is_index_back=false, is_global_index=false,                   |
|       range_key([TBL3.__pk_increment]), range(MIN ; MAX)always true |
|   3 - output([TBL4.COL1], [TBL4.COL2]), filter(nil), rowset=16      |
|       access([TBL4.COL1], [TBL4.COL2]), partitions(p0)              |
|       is_index_back=false, is_global_index=false,                   |
|       range_key([TBL4.COL1]), range(MIN ; MAX)always true           |
+---------------------------------------------------------------------+
23 rows in set

在上面 Q4 和 Q5 查询中使用了 USE_MERGE Hint 指定使用排序合并联接算法。在 Q4 和 Q5 查询返回结果中，除了 MJ 算子（0 号算子），还展示了以下执行算子：

SORT：用于对输入的数据进行排序。详细信息参见 SORT。
TABLE FULL SCAN：该算子属于 TABLE SCAN 算子，用于展示优化器选择哪个索引（或主表）来访问数据。详细信息参见 TABLE SCAN。

在 Q4 和 Q5 执行计划展示中，outputs & filters 详细展示了 MERGE JOIN 算子的具体输出信息如下：

信息名称	含义	示例说明
output	该算子最终输出的列或表达式列表。	`output([TBL3.COL2 + TBL4.COL2])` 表示输出表达式 `TBL3.COL2 + TBL4.COL2` 的结果。
filter	该算子需要应用的过滤条件（谓词）。	`filter(nil)` 表示在完成联接操作后，没有需要再额外过滤的行。
rowset	表示当前算子的向量化大小。	`rowset=16` 表示当前算子的向量化大小为 16。
equal_conds	归并联接时使用的等值联接条件，左右子节点的结果集相对于联接列必须是有序的。	在 Q4 查询中，`equal_conds([TBL3.COL2 = TBL4.COL2])`：表示连接是基于两个表的 `col2` 字段值相等进行的。在 Q5 查询中，`equal_conds([TBL3.COL1 = TBL4.COL1])`：表示连接是基于两个表的 `col1` 字段值相等进行的。
other_conds	表示连接过程中需要满足的其他非等值条件。	在 Q4 查询中，`other_conds([TBL3.COL1 + TBL4.COL1 > 10])` 是一个附加的过滤条件。它要求连接后，两个表 `col1` 字段值的和必须大于 10。在 Q5 查询中，`other_conds(nil)` 表示除了 `equal_conds` 指定的等值连接条件外，没有其他附加条件。
merge_directions	表示进行合并连接（`MERGE JOIN`）时，两侧输入数据的排序（及扫描）方向。	`merge_directions([ASC])`：表示两侧都按照升序进行排序和归并操作。

HASH JOIN

HASH JOIN（简称 HJ）表示哈希连接。

可通过 USE_HASH Hint 强制优化器使用 HJ。USE_HASH Hint 指定的表作为连接的右表时，使用 HASH-JOIN 算法。有关 USE_HASH Hint 的详细介绍信息，参见 Join Operation Hint 下 USE_HASH Hint 章节。

HASH JOIN 示例

创建表 tbl5。

obclient> CREATE TABLE tbl5(col1 INT, col2 INT);

创建表 tbl6，定义列 col1 为主键。

obclient> CREATE TABLE tbl6(col1 INT PRIMARY KEY, col2 INT);

Q6：指定 USE_HASH Hint 使用 HJ 的方式，查询 tbl5.col1 = tbl6.col1 且 tbl5.col2 + tbl6.col2 > 1 的行，并计算 tbl5.col2 + tbl6.col2，同时查看该查询的执行计划。

obclient> EXPLAIN SELECT /*+USE_HASH(tbl5, tbl6)*/ tbl5.col2 + tbl6.col2
    FROM tbl5, tbl6
    WHERE tbl5.col1 = tbl6.col1
    AND tbl5.col2 + tbl6.col2 > 1;

返回结果如下：

+--------------------------------------------------------------------------------------+
| Query Plan                                                                           |
+--------------------------------------------------------------------------------------+
| =================================================                                    |
| |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                    |
| -------------------------------------------------                                    |
| |0 |HASH JOIN        |    |1       |5           |                                    |
| |1 |├─TABLE FULL SCAN|TBL5|1       |3           |                                    |
| |2 |└─TABLE FULL SCAN|TBL6|1       |3           |                                    |
| =================================================                                    |
| Outputs & filters:                                                                   |
| -------------------------------------                                                |
|   0 - output([TBL5.COL2 + TBL6.COL2]), filter(nil), rowset=16                        |
|       equal_conds([TBL5.COL1 = TBL6.COL1]), other_conds([TBL5.COL2 + TBL6.COL2 > 1]) |
|   1 - output([TBL5.COL1], [TBL5.COL2]), filter(nil), rowset=16                       |
|       access([TBL5.COL1], [TBL5.COL2]), partitions(p0)                               |
|       is_index_back=false, is_global_index=false,                                    |
|       range_key([TBL5.__pk_increment]), range(MIN ; MAX)always true                  |
|   2 - output([TBL6.COL1], [TBL6.COL2]), filter(nil), rowset=16                       |
|       access([TBL6.COL1], [TBL6.COL2]), partitions(p0)                               |
|       is_index_back=false, is_global_index=false,                                    |
|       range_key([TBL6.COL1]), range(MIN ; MAX)always true                            |
+--------------------------------------------------------------------------------------+
19 rows in set

在上面 Q6 查询中使用了 USE_HASH Hint 指定使用算法。在返回结果中，除了 HASH JOIN 算子（0 号算子），还展示了以下执行算子：

TABLE FULL SCAN：该算子属于 TABLE SCAN 算子，用于展示优化器选择哪个索引（或主表）来访问数据。详细信息参见 TABLE SCAN。

在 Q6 执行计划展示中，outputs & filters 详细展示了 HASH JOIN 算子的具体输出信息如下：

信息名称	含义	示例说明
output	该算子最终输出的列或表达式列表。	`output([TBL5.COL2 + TBL6.COL2])` 表示此 `HASH JOIN` 完成计算后，向外输出的是表达式 `TBL5.COL2 + TBL6.COL2` 的结果。
filter	该算子需要应用的过滤条件（谓词）。	`filter(nil)` 表示在完成联接操作后，没有需要再额外过滤的行。
rowset	表示当前算子的向量化大小。	`rowset=16` 表示当前算子的向量化大小为 16。
equal_conds	表示哈希连接的等值连接条件，左右两侧的联接列会用于计算哈希值。	`equal_conds([TBL5.COL1 = TBL6.COL1])` 表示哈希连接会以 `TBL5.COL1 = TBL6.COL1` 等值关系来关联两个表的行。
other_conds	表示在哈希连接完成匹配后，对结果集进行过滤的其他联接条件。	`other_conds([TBL5.COL2 + TBL6.COL2 > 1])` 表示 `equal_conds` 指定的等值连接条件外的附加过滤条件。它要求连接后，两个表 `col1` 字段值的和必须大于 1。