Is there any value in table order in sql join statement

Question

Is there any value in table order in sql join statement. for example

SELECT dept_name, emp_name FROM Employee INNER JOIN Department ON Employee.dept_id = Department.dept_id 

and

 SELECT dept_name, emp_name FROM Department INNER JOIN Employee ON Employee.dept_id = Department.dept_id 

Is there any advantage in table performance?

Answer 1

No no.

Most (if not all) DBMSs use a cost-based optimizer. The order in which you state your statements does not affect execution speed.

Cost Optimization Based on Oracle SQL

Oracle Cost-Based SQL Optimizer (CBO) is an extremely complex Oracle component that manages the execution for each Oracle query. CBO has evolved into one of the world's most complex software components, and it has the difficult task of evaluating any SQL and creating the “best” execution plan for approval.

Both of your statements will generate the same execution plan and, therefore, will have the same performance characteristics. Please note that the cost will be based on available statistics. Updated statistics are very important so that the optimizer can create the most effective execution plan.

Answer 2

In general, no, it does not matter. The optimizer should be able to determine the most efficient table join order, regardless of the order that they display in the query.

It is possible, however, that the order of the tables will affect the query plan. As a rule, this is not so if you have a simple join of two tables, but as the number of tables in a query increases, the number of possible joins grows with a frequency of O (n!). Quite quickly, the optimizer becomes unable to consider all possible join orders, so it needs to use various heuristics to trim the tree. This, in turn, leads to situations where the optimizer selects another driving table if this table is listed first in the SQL expression, and not when this table is the tenth table in the query. Jonathan Lewis has a good blog post showing how the order tables displayed in a query can affect the query plan . If you want to be overly careful, first bringing the table to the driver is a reasonable thing - it will not help very often, but it can sometimes bring some benefit.

Answer 3

The optimizer checks all possible permutations of the join order and takes the one with the lowest cost value. This means that self-optimization — preparing a statement — is becoming a bottleneck for complex operators. The more tables to join, the more options for the execution plan can be checked mathematically: n! (Factorial).

source: http://use-the-index-luke.com/sql/join

Answer 4

Not. The optimizer determines the best connection path, or at least what it considers the best connection path. In unusual situations, sometimes he does not have all the necessary information, but in 99% of cases this will be correct. It will also internally rewrite the SQL statement in other ways.

Answer 5

as others say, no.
but think about whether you can replace some of the inner joins with left joins. in most queries this is a performance advantage