How to Use Hints to Specify Join Order in SQL with Subqueries

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 存在子查询的SQL如何使用Hints指定关联顺序

| username: FutureDB

How to specify the join order through Hints? For example, the specified join order is: t2 (subquery alias), t4, t1, t3.

| username: vincentLi | Original post link

Why use a hint for this? Shouldn’t the order of the statements be adjusted instead?

| username: vincentLi | Original post link

## Join Order
Here you might find the information you need.

| username: TiDBer_H5NdJb5Q | Original post link

Why does the SQL need to be written this way? Is it due to business considerations?

| username: tidb菜鸟一只 | Original post link

SELECT
/*+ LEADING(t2, t4,t1,t3) / t1.
FROM t1
JOIN (
SELECT *
FROM table2
WHERE condition
) AS t2 ON t1.id = t2.id
JOIN t3 ON t1.id = t3.id
JOIN t4 ON t2.id = t4.id
like this

| username: FutureDB | Original post link

The table t2 is an alias for the subquery result, and directly specifying it in the leading clause does not work.

| username: tidb菜鸟一只 | Original post link

Is it written like this?

| username: FutureDB | Original post link

I roughly understand your point. You mean to proactively remove the subquery, but what I mainly want to ask is how to specify the join order when there is a subquery?

| username: 小于同学 | Original post link

I don’t understand what these joins are for.

| username: Jellybean | Original post link

If you want to execute the join in a specified order, you can consider using the straight join method to specify it.

| username: 有猫万事足 | Original post link

Try adding a hint like this to see if it works.

| username: FutureDB | Original post link

Although it is necessary to change the order of the SQL writing, the STRAIGHT_JOIN() method is indeed a good idea; I had been considering leading, but currently, leading cannot specify the association order of such subqueries.