First I would like to cite the existing post Table order in Join Query

so the author is saying that the table order really doesn't make things(result, access plan)different. I have doubt on this regarding the access plan.

so the documentation of DB2 (TABLE JOIN) tells in the NLJoin, outer table is fixed and scanned only once, while the inner tables might be scanned multiple times depend on different scenarios, under this circumstance, I think the order of tables in the join really matters, we need to put big table as outer table, as we only want to scan this once.

is this the case, is this only for DB2? did I miss something?

+is there general guidance when writing SQL, how should we deal with the table join orders ?


You are right that for Nested Loop Join, the choice of which table is the inner and which the outer table matters for perforamnce.

However, there is nothing in the documentation, in the link you provided, that implies that for a query that has a INNER JOIN b, the table a will be used as inner and b as outer table when the Nested Loop Join algorithm is selected.

Any decent optimizer evaluates many different combinations of algorithms, placing of tables and order of execution, so I don't think there is any difference if you write a INNER JOIN b or b INNER JOIN a, the chosen execution plans should be the same in both cases. If there are exceptions to this, I would expect them to be for very complex queries with tens of joined tables and/or multiple groupings.

Testing and checking the actual execution plans is one way to confirm this. Another would be to analyze the source code of the query optimizer.

The general guidance when writing SQL (in whatever DBMS), is not to care at all about the table join orders. SQL code describes what you want as result, it doesn't tell the DBMS how to get it. And many optimizers now are really smarter and faster than most of us in choosing the best execution plan most of the time.

Unless documentation shows that the optimizer is naive or in a very early version and the way the queries are written, really affects the chosen execution plan.

Or testing/running of a specific query shows that it's slow and some obviously good plan was not chosen. Then you can experiment with hints (if the DBMS has such feature), try rewriting the query in different ways, check if statistics are updated, etc.

  • so there is no general guideline as how the table should be placed ?
    – zinking
    Jun 27 '12 at 6:21
  • 2
    In DB2 running stats frequently is important as statistics gathered by the database engine may influence how the optimizer picks its access path. Jun 27 '12 at 12:51
  • @Chris: thnx for the point. I see you have used DB2 a lot, so you surely have more insight on this. Jun 27 '12 at 13:07
  • Which order the optimizer chooses to process them also may depend on what indexes exist over the tables in question.
    – WarrenT
    Sep 5 '12 at 5:38

Well, the question you first highlight is really for SQLServer, so DB2 may use a different algorithm for determining join order, type of join, and optimization plans.

The documentation you found on DB2 is correct. I would also check out this link on strategies for selecting optimal joins. Even though this was for an older version of DB2, I have found this article incredibly helpful with understanding how/when DB2 picks which join to use and what influences its choice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.