NULL In IN clause

[akshaycjoshi]

I have a table called NUMS with a single column n.
And I fill values 1,2,3,4,5,null in it.

Consider a query
SELECT n FROM Nums
WHERE n NOT IN (1, 2, null)


In this case I guess it's converted to

SELECT n FROM Nums
Where NOT(n = 1 OR n = 2 OR n = null)

The comparison n=null will evaluate UNKNOWN for all the values on n
including null.

Negating UNKNOWN returns unknown and therefore it returns empty set.



Consider the opposite case
SELECT n FROM Nums WHERE n IN(1, 2, null)

Here also the comparison is being performed so it should return unknown.
SO the whole result set should be empty.

But it's returning 1 and 2.

Can anyone explain in detail what's happening.

[ck9663]

Here, read this and this

Happy Coding!!!

~~ CK