sql-serverdatabaset-sqljoinjoin-hints

TSQL - What is the proper order to joining tables?


My google-fu and so-fu is failing me here, so I might as well ask.

I have many queries with multiple joins in them.

Within one query, I am joining header/item/details together, as well as looking up various bits of information for these records.

When joining, I try to keep things in order of how their related. E.g.: My header has two lookup tables, so I'll join to those before joining to my items table.

Is that correct?

Is it better to join to larger tables before lookup tables? Or vice-versa?

Should I use a loop hint when joining to small tables, and a merge hint when joining to openrowsets?

I'm sure the answer is "it depends", but some general guidelines to joining effectively and efficiently would be very helpful. Thanks!


Solution

  • EDIT: Based on your questions (and Kirk Woll's) comment, you should understand that the order of your joins is aesthetic, and does not directly impact the resulting execution plan. The query optimizer will perform the joins in the most efficient order, and use the appropriate join operation the vast majority of the time without any hints needed.

    When it comes the the aesthetics of your join order this is a little subjective, but I would say join your tables together in whatever order makes logical sense when reading through the query... if you start with a @HeaderId, start with that table, and then JOIN to the children tables:

    FROM
        Header h
        JOIN Items i ON h.HeaderId = i.HeaderId
        JOIN Details d ON i.ItemId = d.ItemId
    WHERE
        h.HeaderId = @HeaderId
    

    But, if you started your query with a @DetailId, I would join in the opposite order.

    FROM
        Details d
        JOIN Items i ON d.ItemId = i.ItemId
        JOIN Header h ON i.HeaderId = h.HeaderId
    WHERE
        d.DetailId = @DetailId
    

    However, that's subjective and just my personal preference.

    It becomes less subjective when you start to include OUTER JOINs... try to structure your query to avoid any RIGHT OUTER JOINs, and instead use LEFT OUTER JOIN's.

    Don't use join hints by default... in fact you'll almost never use them. The query optimizer does a very good job of choosing the optimal execution plan. I've only encountered a single instance where I needed to provide a join hint to improve the plan... it helped drastically on the server that the query was running on at the time, but when the database was migrated to a different server, my join hint destroyed the performance of the query. So, to reiterate, it is usually a bad idea to provide join hints.