What Is Merge Join
Merge Join, also known as Sort Merge Join, is a Join algorithm based on sorting and matching in multi-table queries. Its core steps are: first, sort the two associated tables separately by the Join key, and then sequentially read and match data from the two sorted result sets.
Unlike Hash Join, Merge Join relies on the sorted order of input data. When the Join keys are inherently sorted (for example, when the Join keys are the primary keys of both tables and the query execution plan already includes the corresponding sorted inputs), Merge Join can directly perform the merge match without additional sorting, delivering better performance than Hash Join. Conversely, if the Join keys are unsorted, Merge Join must first incur the cost of sorting, and its overall performance is typically inferior to that of Hash Join.
The columnar read-only analysis instance supports Merge Join in primary key scenarios. This join algorithm is automatically selected by the optimizer when conditions are met, and can also be forcibly enabled via a Hint.
Use Cases
Merge Join is applicable to the following query scenarios:
The Join type is equi-Join (only the = predicate is supported, for example A.a = B.a). Range joins and NULL-safe equi-joins (<=>) are not supported.
When the Join key is the primary key of both tables or shares the same sorting prefix, the input data is inherently sorted, allowing the additional sorting step to be omitted.
The data types of the corresponding columns on both sides of the Join must be consistent. Inconsistent types may prevent the optimizer from selecting Merge Join.
Attention:
Merge Join cannot be used in the following scenarios:
The Join column is of type ENUM or SET.
The Join condition contains <=> (NULL-safe equality comparison).
The Join condition is a non-equality expression (for example, >, <, or BETWEEN).
Usage
You can force the use of Merge Join via a Hint. Equivalent notations include merge_join, libra_smj, and use_merge. The parameter is a list of the table names or aliases participating in the Join.
Example: Force orders and lineitem to use Merge Join.
SELECT *
FROM orders o, lineitem l
WHERE o.o_orderkey = l.l_orderkey;
Equivalent notations:
SELECT * FROM orders o, lineitem l WHERE o.o_orderkey = l.l_orderkey;
SELECT * FROM orders o, lineitem l WHERE o.o_orderkey = l.l_orderkey;
To prevent a specific Join from using Merge Join, you can use the negative Hint no_use_merge:
SELECT *
FROM orders o, lineitem l
WHERE o.o_orderkey = l.l_orderkey;
Validating Execution Plans
To view the Join operator using the EXPLAIN command, check the output. If Merge Join is actually selected, the output will contain the text MergeJoin_*:
EXPLAIN SELECT *
FROM orders o, lineitem l
WHERE o.o_orderkey = l.l_orderkey;
Note:
When the Join method specified by a Hint does not meet the prerequisites (for example, non-equality Join keys or incompatible Join key types), the optimizer ignores the Hint and falls back to Hash Join. It also outputs a warning message. You can view the reason by using SHOW WARNINGS.