By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,440 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

NULL In IN clause

100+
P: 153
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.
Feb 4 '10 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
Here, read this and this

Happy Coding!!!

~~ CK
Feb 4 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.