STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. Use it in the rare cases where the optimizer joins tables in a suboptimal order.
The execution result of STRAIGHT_JOIN is the same as that of INNER JOIN. The difference is that INNER JOIN lets the optimizer reorder tables for efficiency, while STRAIGHT_JOIN fixes the order exactly as written.
Prerequisites
Before you begin, ensure that you have:
An AnalyticDB for MySQL cluster of version 3.1.3.0 or later
To check the minor engine version of your cluster, see How can I view the version of an AnalyticDB for MySQL cluster?. To update the minor engine version, contact technical support.
Syntax
join_table:
table_reference STRAIGHT_JOIN table_factor [join_condition]
table_reference:
table_factor
| join_table
table_factor:
tbl_name [alias]
| table_subquery alias
| ( table_references )
join_condition:
ON expressionIn a STRAIGHT_JOIN b, table a (the left table) is always read before table b (the right table). The optimizer does not reorder the join.
When to use STRAIGHT_JOIN
Use STRAIGHT_JOIN when the left and right tables that have specific sizes are specified for INNER JOIN, or when you want to change the join order of tables in an AnalyticDB for MySQL execution plan.
Performance guidance by join algorithm
The join algorithm affects which table should be on which side:
| Join algorithm | Left table | Right table |
|---|---|---|
| Hash join | Larger | Smaller |
| Nested-loop join | Smaller | Larger |
Examples
Override join order with STRAIGHT_JOIN
Suppose the optimal join order is region → nation → customer, but the optimizer chooses a different order. Use STRAIGHT_JOIN to enforce the correct sequence:
-- Original query: optimizer chooses join order automatically
SELECT count(*)
FROM customer, nation, region
WHERE c_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA';
-- Rewritten with STRAIGHT_JOIN: join order is fixed as region → nation → customer
SELECT count(*)
FROM region STRAIGHT_JOIN nation ON n_regionkey = r_regionkey
STRAIGHT_JOIN customer ON c_nationkey = n_nationkey
WHERE r_name = 'ASIA';Mix STRAIGHT_JOIN and INNER JOIN
STRAIGHT_JOIN and INNER JOIN can appear in the same query. In this example, STRAIGHT_JOIN generates the same results as INNER JOIN. For the STRAIGHT_JOIN part, the optimizer does not automatically adjust the join order and the region table is used as the left table. For the INNER JOIN part, the optimizer determines the most efficient order in which to join tables and automatically adjusts the join order.
SELECT count(*)
FROM region STRAIGHT_JOIN nation ON n_regionkey = r_regionkey
INNER JOIN customer ON c_nationkey = n_nationkey
WHERE r_name = 'ASIA';