Why Performance Tuners Need To Use The Right Type Of Join In SQL Server

[@hannahvernon]

I really love seeing a LEFT JOIN with the referenced table appearing in a WHERE clause. I mean, that essentially *is* an INNER JOIN.

[@ErikDarlingData]

Unless it’s where something is null, and then it’s just a 💩

[@mkeyx82]

I've had some good fortune working with queries that used extra (but necessary) left joins (where it seemed like adding 6th or 7th left join made things exponentially worse; incredibly, it seems the same thing happens when drinking beer, everything is fine after the first 3 or 4) and replacing them with outer apply. Which again can introduce problems of its own, but the pay off seems higher when there's a subselect that is being left joined. Didn't know about the merge join predicament. I'll keep an eye out for that little s.o.b. Thanks.

[@ErikDarlingData]

Yep, it’s a pesky thing for sure.

[@brainsniffer]

Erik recommends every query needs a right join? Sounds good to me!

[@ErikDarlingData]

😂😂😂

[@zammea]

Hi, mind me asking but way back I saw a code structured like that select columns from tableA left outer join tableB on a.id = b.ref_id where b.ref_id is null I was wondering is this a wrong way of writhing a.id not in (select b.ref_id from tableB)?

[@ErikDarlingData]

Check out this post to learn more about that: erikdarling.com/the-how-to-write-sql-server-queries-correctly-cheat-sheet-exists-and-not-exists/

[@zammea]

@@ErikDarlingData Thank you.

[@caparn100]

Merge joins are excellent if joining two tables on a clustered index when they have the right columns for the join.

[@ErikDarlingData]

Sort of like in this query plan where they’re not awesome? 😉

[@mrfuzztone]

The INNER JOIN was faster because of the Bitmap filter used by the hash join. You did remind me of why an INNER JOIN can be faster than a LEFT JOIN when appropriate. Good real world example. Of course an INNER JOIN might perform identical to a LEFT JOIN. Depends.

[@ErikDarlingData]

Yes, many things do seem to depend.

[@seancarroll6843]

Never knew about the parallel merge join pitfall... did MS just think that having a plan going parallel and risking the thread deadlock is "always" better than defaulting to a serial query because it wants to use a merge join? FYI if everyone thinks you're "darlene data" maybe you're off the hook with beer gut magazine.

[@ErikDarlingData]

well, it's all about costing, and the optimizer doesn't consider the *chance* of a parallel deadlock when choosing plans. if i had anything to say about it, once parallel plan exploration started, merge join costs would increase significantly. though i think they view it as more of a challenge to fix parallel deadlocks to avoid merge joins. i've added a link to the video with more details about exchange spills for reference.

[@seancarroll6843]

@@ErikDarlingData Thanks! I appreciate the additional info!

[@seannalexander]

OPTION (OPTIMIZE FOR UNKNOWN) HELLO I AM SEE(A)N

[@ErikDarlingData]

I can see into the UNKNOWN

[@FlaggedStar]

I think you've just accidentally explained why I can never get table variables to merge join. If you can't go parallel, then you probably won't merge join.

[@ErikDarlingData]

No, that’s completely wrong thinking. You don’t need a parallel plan to get a merge join, and there’s no restriction on parallel select plans from table variables. More likely is you don’t have a supporting index for the Merge join, and that the low cardinality estimates from them make nested loops seem cheaper.