Skip to Main Content
  • Questions
  • Is there any difference in the execution plan (for better or worse) the order of tables in the from clause?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ulises.

Asked: January 19, 2017 - 10:27 pm UTC

Last updated: January 22, 2017 - 3:10 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Is there any difference doing:

SELECT a.col1, b.col1 FROM table1 a, table2 b
WHERE <join_and/or_filter_clauses>

and

SELECT a.col1, b.col1 FROM table2 b, table1 a
WHERE <join_and/or_filter_clauses>

Are the sizes of the tables importante for the order they appear into the FROM clause?

Are the order of WHERE AND/OR clauses importante too?

Is there any general rule for this order of things?

All these related to performance.

and Connor said...

Nope. The beauty of SQL (under the cost optimizer, which is the default) is that order of tables and/or predicates is irrelevant.

I write table generally in the order that I would make the job of the next programmer who looks at my code easier. So I'd typically write:

from  GRANDPARENT,
      PARENT,
      CHILD


for example, rather then something like:

from  PARENT,
      GRANDPARENT,
      CHILD


but this is purely to help convey the "natural" join order.

There are some small niche cases where the order might make a difference, but these are typically bugs rather than anything a developer should be catering for.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Moreover

Gh.., January 21, 2017 - 2:40 pm UTC

It is very important to add WHY.
Nope. Because the oracle engine rewrite the query... otherwise YEAH unless the ORDERED hint would not exist.

Unless......

Tubby, January 21, 2017 - 5:29 pm UTC

Of course there's always an exception to the rule :)

https://jonathanlewis.wordpress.com/2017/01/10/join-elimination-12-2/

Not something I'd advocate worrying about when generally writing queries, but like anything it's good to know that there can be exceptional instances where things like this come in to play.

Cheers,
Connor McDonald
January 22, 2017 - 3:10 am UTC

Yup...hence my caveat in the original answer:

"There are some small niche cases where the order might make a difference"

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.