Tell the join how to join.

I have been having a lot issues…with query execution plans lately (using CF and SQL). SQL has been choosing shitty plans thus making my query slow. If you have ever looked at execution plans, they are difficult read...unless you are a DB developer. I have had no idea wtf was going on with these plans…until now. Another developer sent this great link explaining what the joins are doing in the execution plan. You can then tell your joins in your query how to join.

Example:
select * from [user]
inner hash join enterprise_user on enterprise_user.user_id = [user].id

An execution plan does more than just join identification, such as indexes, but the join thingy is all I really cared about at the moment.

P.S. Only specify the “how to join” if you have to; if your query blows at running, let SQL try to calculate the best plan.

Use Case: Had a query in cfquery tags and it was choosing an execution plan with nested loop joins instead of the fast hash joins.