SQL - records not in another table

Question

In doing what I thought was a fairly routine task of finding records in one table that do not exist in another table, I started out like the following. Note: the common value is of type float in one table (thus the cast) and varchar(20) in the other. MEMID=float, DMID=varchar.

select cast((convert(int, MEMID)) as varchar(20))
from tempProv111315
where cast((convert(int, MEMID)) as varchar(20)) NOT IN (
     SELECT DMID
     FROM tempMemberMaster121015
)

The above returned no records (even though i knew a few existed). Therefore I tried the following (which worked).

select cast((convert(int, pv.MEMID)) as varchar(20))
from tempProv111315 pv
left outer join  tempMemberMaster121015 mm on
   cast((convert(int, pv.MEMID)) as varchar(20)) = mm.dmid
where mm.dmid is null

Although likely a simple reason, for the life of me I cannot see why the first way didn't work. I'm not a dba but have used both ways often in different circumstances so I really want to understand what I'm missing. Can anyone explain the different outcome as they seem logically equal to me.

ps - i'm familiar with several ways to cast the float. This is my typical method. I don't believe it is the issue... but if so, why does it work okay on the second statement?

Thanks!

Answer 1

Its a known behavior, your sub-query would have returned NULL which makes the not in to fail. More info can be found here

NOT IN
(
     SELECT DMID -- check whether this returns any NULL value
     FROM tempMemberMaster121015
)

I will suggest you to use NOT EXIST instead of Left Outer Join