Posts

Showing posts from January, 2015

Ambiguous IN functionality

Here is an interesting item that ended up biting me last week. This is definitely unexpected behavior from my understanding, but I wanted to make sure others were aware to avoid the problems. This code assumes you have a tally table called dbo.numbers with a single integer column named [Num].
--Set up a Temp Table so that we can limit the information we are getting out If OBJECT_ID('tempdb..#NUMCheck') IS NOT NULL DROP TABLE #NUMCheck Create Table #NUMCheck (Number Int) --a Simple IN using a Subquery Select top 100 * from dbo.Numbers where Num in (select Num From #NUMCheck ) --Oops, i forgot to load anything into the Table Insert Into #NUMCheck (Number) Select 2 Union all Select 3 Union all Select 5 Union all select 7 Union all Select 11 --OK, try it again Select top 100 * from dbo.Numbers where Num in (select Num From #NUMCheck ) --?? Oh wait, the Temp Table uses Number not Num --and how it should be written anyway Select top 100 * from dbo.Numbers where Num in (select …