SQLNightOwl (2/11/2016)
You may want to consider performance differences between using an IN and an INNER JOINselect columns
from dbo.sometable
where ItemCode in (select Item from #tempTable)
-- AND --
select columns
from dbo.sometable st
inner
join #tempTable tt
on st.ItemCode = tt.Item
... may yield different performance benchmarks. The differences could vary bases on the number of items in the temp table, if the temp table uses that value as a primary key and if the permanent table has a SARGable index. STATISTICS IO and TIME can give you some of that information.
It depends.
http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/[/url]
Always study the execution plan, always test the alternative options, and always test on a set which is representative of production data and then some.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden