Does the sequence in which we use join in a query effects its execution time ?

Question

Does the sequence in which we use join in a query effects its execution time ?

Answer 1

Yes it does. Its effect can be seen in the query execution plan. Refer this and this. Another link is here

Answer 2

No, it does not.

SQL Server's optimizer picks the best (in its opinion) way regardless on the join order.

SQL Server supports a special hint, FORCE ORDER, which makes the tables to lead in the joins in the order they are listed.

These queries:

SELECT  *
FROM    t_a
JOIN    t_b
ON      a = b
OPTION (FORCE ORDER)

and

SELECT  *
FROM    t_b
JOIN    t_a
ON      a = b
OPTION (FORCE ORDER)

will produce identical plans with the OPTION (FORCE ORDER) omitted and different plans with that added.

However, you should use this hint only when you absolutely sure you know what you are doing.

Answer 3

The optimizer will usually compare all join orders and try to select the optimal order regardless of the order you wrote the query however in some complicated queries there are simply too many options to consider, note that the number of possible join orders increases to the factorial of the number of tables joined. In this case the optimizer will not consider all options but will definitely consider the order you wrote the joins in the query and therefor may effect execution plan and execution time.

Answer 4

For outer joins, the order of tables changes the meaning of your query and is very likely to change the execution plan.