Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011

    Unanswered: The order of joining tables

    I have 6 tables.

    The main one is I_detail. That table joins R-Type, commodity, region, and contract. Ads join contract.

    So far I have them in this order:

    I_detail join Contract

    Contract join Ads

    I_detail join region

    I_detail join R-Type

    I_detail join commodity

    or should I join everything related to I-retail first and then join the ads and contract at the end?

    Please let me know. Thank you.

  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    The answer is that it probably does not matter because the Query Engine is usually smart enough to get it right and process them in whatever order it wants to regardless of what you type. On the rare occassion that I have used FORCEORDER it was due to performance problems incurred on very wide and large tables with many indexes. SQL Server would not always find the "best execution plan" because it would find one it deemed "good enough" while calculating all of the possiblities. And good enough was not good enough.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    SQL should be used to specify WHAT you want returned, and definitely NOT how to get it. The only exceptions to this rule should be when for some reason (like Thrasymachus pointed out above) the database engine falls down and needs some help from the person writing the code. I've written tens or maybe hundreds of thousands of SQL statements, and have probably needed to "help" five or six of them.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jul 2011
    Thanks for the reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts