This topic describes recursive query that is supported by AnalyticDB for MySQL clusters of version 3.1.3 and later. Similar to the JOIN statement, the STRAIGHT_JOIN statement does not adjust the sequence of the left and right tables in the execution plan. STRAIGHT_JOIN can be used to join optimizers when optimizers process tables in descending order.

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 
Note
  • The execution result of STRAIGHT_JOIN is the same as the execution result of INNER JOIN.
  • When the a STRAIGHT_JOIN b syntax is executed, Table A is the left table, and Table B is the right table to join each other. Table A and table B are directly joined. The optimizer does not optimize the join order.

Scenarios

This syntax specifies the left and right tables when INNER JOIN is executed. This syntax can be used to specify the table size or to check whether the left and right tables selected by INNER JOIN in the execution plan of AnalyticDB for MySQL are found unreasonable.

By default, you can choose a large table to the left and a small table to the right in a hash join scenario. This way, AnalyticDB for MySQL can achieve better performance. If nested loop join is specified, select a small table to the left and a large table to the right.

If the best join order is region, nation, or customer in the following SQL statement, you can make queries more efficient, modify the first SQL statement to the second SQL statement and specify the join order.

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 the results of INNER JOIN. Then, the optimizer determines whether the result table generated by STRAIGHT_JOIN is the same as the result table of the left and right tables when the customer table is joined.

SELECT count(*)
       FROM    region STRAIGHT_JOIN nation on n_regionkey = r_regionkey
       INNER JOIN customer ON c_nationkey = n_nationkey
       WHERE r_name = 'ASIA';