STRAIGHT_JOIN is similar to JOIN. The only difference is that STRAIGHT_JOIN does not adjust the order in which tables are joined in the execution plan. STRAIGHT_JOIN can be used to speed up a query if the optimizer joins tables in a sub-optimal order.
Prerequisites
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 expression
- The execution result of STRAIGHT_JOIN is the same as that of INNER JOIN.
- In the
a STRAIGHT_JOIN b
statement, Table a that serves as the left table is joined with Table b that serves as the right table. The optimizer does not optimize the join order.
Scenarios
- You can use STRAIGHT_JOIN if the left and right tables that have specific sizes are specified for INNER JOIN. You can also use STRAIGHT_JOIN to change the join order of tables that are specified for INNER JOIN in an AnalyticDB for MySQL execution plan.
- To achieve better performance, specify a larger left table and a smaller right table for hash joins, and a smaller left table and a larger right table for nested-loop joins.
Assume that the optimal join order of tables is region, nation, and customer. To improve query efficiency, you can change the first statement to the second one by using STRAIGHT_JOIN and specifying the join order of tables.
SELECT count(*)
FROM customer, nation, region
WHERE c_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA';
SELECT count(*)
FROM region STRAIGHT_JOIN nation on n_regionkey = r_regionkey
STRAIGHT_JOIN customer ON c_nationkey = n_nationkey
WHERE r_name = 'ASIA';
Examples
In this example, STRAIGHT_JOIN generates the same results as INNER JOIN. For the STRAIGHT_JOIN query, the optimizer does not automatically adjust the join order. The region table is used as the left table. For the INNER JOIN query, 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';