CODEWITHSUNDEEP
sqlsql-servert-sql
Yashar Aliabbasi
Yashar Aliabbasi

Reputation: 2739

Why NOT IN (NULL) Always returns nothing

I have following table:
My Table
And following simple query:

SELECT * FROM dbo.Calendars WHERE calname NOT IN(NULL)

My question is why always NOT IN(NULL) return nothing?
PS:No matter what is your table,if you add NOT IN(NULL) to any column's condition,result is nothing. Thank you in advance :)

Upvotes: 1

Views: 1946

Answers (3)

Dishonered
Dishonered

Reputation: 8851

Your query evaluates to calname <> null , which results in UNKNOWN.

Change your query to this , 

SELECT * FROM dbo.Calendars WHERE calname IS NOT NULL;

Upvotes: 3

kabanus
kabanus

Reputation: 26005

Because value equals NULL is not defined, and will never evaluate to True. Check This post on bad practices. Don't use IN and NULL, use EXISTS to test for at least one row where something exists.

Upvotes: 8

e_i_pi
e_i_pi

Reputation: 4820

NULL is treated differently to other values in most databases. For instance, this predicate will never be true:

WHERE foo = NULL

whereas this predicate is true if the value of foo is indeed NULL:

WHERE foo IS NULL

To your problem, if you only wish to check if calname is not null, use the following predicate:

WHERE calname IS NOT NULL

If on the other hand you have a set of values, of which NULL is one of those values (say [1,2,3,4,NULL]), and you still want to use the NOT IN syntax, you have to do it like this:

WHERE calname IS NOT NULL AND calname NOT IN(1, 2, 3, 4)

EDIT: A further way of doing this, if you are constrained to use the NOT IN syntax is to COALESCE the column calname into a value that you definitely know is not stored in that column. For instance, if calname can only take positive integer values, then we can do this instead:

WHERE COALESCE(calname, -1) NOT IN (-1)

Upvotes: 3

Related Questions