Join order will lead to different query performance

Question

I have 2 tables (table1 table2), table2 is larger than table1.

Script 1:

SELECT t1.A, t2.B FROM table1 AS t1 JOIN table2 AS t2 ON t1.PK = t2.FK 

Script 2:

 SELECT t1.A, t2.B FROM table2 AS t2 JOIN table1 AS t1 ON t1.PK = t2.FK 

Will script # 1 performance be better than script # 2?

Answer 1

You are using INNER JOIN , so the answer is NO .

This will result in exactly the same amount of data due to the type of connection, and you join the same relationships in both queries, so they are basically identical.

It would be different if you used LEFT JOIN , because all data from the main (left) table and all the corresponding data from the details of the table (right) are stored in the left join.

So, if you used a left join and placed a large table on the left side, then the query will produce more data and will probably be slower than putting the large table in the right direction.

Answer 2

He must create the same plan. But if you have many joins, shuffling the join order can change the plan due to the huge number of possible ways to handle these joins.

See. Optimization of orders for participation

Of course, this is only for internal joins; external joins can return different results. Btw, the optimizer should love external connections, which significantly reduces the number of possible connection orders :-)

Answer 3

The inner union is commutative, which means the connection b = b joins a. A logical union may be an inner join, but in terms of execution sql may try to consider nested loops, a hash, or a merge based on several parameters. physical connection is bad, each of them has its own advantages.

One thing you can take care of on your part (minimum) is to try to find out if both columns of the join are indexed, sargabilty, there are no implicit conversions.