TSQL - What is the proper order to joining tables?

Question

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!

Answer 1

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.