Is there any significance in the order of table in sql join statement

Question

Is there any significance in the order of table 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 is any performance advantage in the order of tables?

Answer 1

No there isn't.

Most (if not all) DBMS's use of a Cost based optimizer. The order in which you specify your statements does not affect speed of execution.

Oracle SQL cost based optimization

Oracle's cost-based SQL optimizer (CBO) is an extremely sophisticated component of Oracle that governs the execution for every Oracle query. The CBO has evolved into one of the world's most sophisticated software components, and it has the challenging job of evaluating any SQL statement and generating the "best" execution plan for the statement.

Both your statements will generate the same execution plan and hence have the same perfomance characteristics. Note that the cost will be based on available statistics. Updated statistics is very important for the optimizer to be able to generate the most efficient execution plan.

Answer 2

In general, no it won't matter. The optimizer should be able to figure out the most efficient order in which to join the tables regardless of the order they appear in the query.

It is possible, however, that the order of the tables will affect the query plan. This generally wouldn't be the case if you have a simple two table join but as the number of tables in a query increase, the number of possible joins grows at an O(n!) rate. Pretty quickly, it becomes impossible for the optimizer to consider all possible join orders so it has to use various heuristics to prune the tree. That, in turn, leads to situations where the optimizer picks a different driving table if that table is listed first in the SQL statement as opposed to when that table is the tenth table in the query. Jonathan Lewis has a nice blog post showing how the order tables appear in a query can affect the query plan. If you want to be extra-careful, listing the driving table first is a reasonable thing to do-- it won't help very frequently but it may occasionally do some good.