IN vs Union All in SQL Server

One option for improving query performance is to rewrite the query in order to obtain a completely different execution plan. In this episode we look at how it's possible to rewrite queries with correlated subqueries into derived table syntax and examine what causes the difference in performance.
Subscribe and turn on notifications to never miss a weekly video: ruclips.net/user/DataWithBer...
Related blog post with example code:
bertwagner.com/2019/04/23/cor...
Follow me on Twitter:
/ bertwagner

[@barbbq1783]

Learnt something new there. Thank you so much.

[@nofatchicks6]

Thanks Bert, great video as usual.

[@alirezaghanbarzadeh1679]

Great analysis

[@oresteszoupanos]

Hmm, I wonder why the initial Clustered Scan doesn't also pull out the few Research Assistants we are looking for, since it's hitting every row in the table... I might have to test with that one! Intriguing!

[@marcosoliveira8731]

Really nice explanation.

[@RussianVideoPodcast]

Thank you for this. Are there definite scenarios where one should use union vs union all. I understand the general rules, however now at my new place of employment, I am working with queries written by someone else some time ago (that person is no longer with the company) where only UNION is used and never union all. What rule of thumb do you normally use when deciding to use UNION or UNION ALL? Thank you.

[@RonaldJon12]

How do you handle query caching when judging performance? Changing the where clause usually works but was wondering if you had any other tricks.

[@stefanotorelli3688]

Great explanation! One question.... why? I ask why, new versions of SQL Server are performing so bad? ...I know... bad practs... ;)