Join Hints in SQL: Invade the query optimizer!

You can use join hints to tell the query optimizer how you want join operation to be performed. Depending on your preference, it will come up with an execution plan.

There are four types of join hints, namely LOOP, HASH, MERGE and REMOTE.

You may need to decide which join hint would optimize your query based on the type and size of data you are processing.

How ever, it is advisible not to use this feature frequently, unless you have a significant performance improvement, as SQL Server tries it's best to use the best possible join hint by default, mostly it is LOOP.

I have given some execution plans related to different Join hint types below: