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 Number From #NUMCheck)

--So it looks like if the Column does not exist in the Temp table, it will just pull from the FROM table
--No Comment on that the Column does not Exist
--No Error at Run time stating that the Column does not exist in the Temp table in the Sub Query

--But what if it did have the Same Name
If OBJECT_ID('tempdb..#NUMCheck2') IS NOT NULL DROP TABLE #NUMCheck2
Create Table #NUMCheck2
(Num Int)

Insert Into #NUMCheck2
(Num)
select 7
Union all Select 11

--and try it again
Select top 100 * from dbo.Numbers
where Num in (select Num From #NUMCheck2 )
--Why would that work, shouldn't it state that it was an Ambiguous column

/*
Select top 100 * 
from dbo.Numbers a
Inner Join #NUMCheck2 b
    on Num = Num
    
--well At least that Still works    
*/    


--so a slip up of the Column name in a Sub query can run without telling you it is an Issue
--Imagine this in a Delete Statement…