Good question that demonstrates the pitfalls of NULL values in IN.

I have to agree with vk-kirov that the explanation is wrong. The result of the query has nothing to do with mismatch in data types, but is caused by the query processor converting the NOT IN statement to a series of <> operators.

Just to prove that is has nothing to do with non-matching data types, but everything to do with the NULL value:

create table #table(id int)

insert into #table

values

(1),

(2),

(3),

(4),

(5),

(null)

declare @notin int

-- The data type of @notin and column [id] matches, but still the query returns no rows.

-- The execution plan shows why (the <> comparison with the NULL valued @notin variable)

select * from #table

where id not in (@notin,1)