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

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?

Answers


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.


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.


The optimizer will check all possible join order permutations and take the one with the lowest cost value. That means that optimizing itself—preparing the statement—becomes a bottleneck for complex statements. The more tables to join, the more execution plan variants to check—mathematically speaking: n! (Factorial).

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


No. The optimizer figures out the best join path, or at least what it thinks is the best join path. In unusual situations, sometimes it doesn't have all the information it needs, but 99% of the time it will get it right. It will internally rewrite the SQL statement in other ways as well.


as other anwsers says, no. but think if you can replace some inner joins with left joins. in most querys this is a benefict in performance


Need Your Help

How to utilize multiple software serial in libraries in Arduino?

gps serial-port arduino

I'm currently doing a project where I interface a GPS module, digital compass and a ZigBee (XBee modem) to Arduino. BTW, I'm using the Eclipse IDE to do this project, so I've broken down my code into

How class loading is done by using -Xbootclasspath/p:path?

java dynamic-class-loaders

I've seen -Xbootclasspath/p:path being used for loading class dynamically can you please elaborate and explain by providing example.