T-SQL: null != value evaluates false [modified]

[Syed Mehroz Alam]

We all know that null, when compared with anything, returns false. Even null=null evaluates to false. But I was amazed to see that null!=Value also returns false. Run the following T-SQL statements and you will get false for both the cases.

print (Case When null ='Value' Then 'true' else 'false' end)
print (Case When null**!=**'Value' Then 'true' else 'false' end)

The query was something like:

Select ...
Case When Column1='SomeValue' .. Then ..
Case When Column1!='SomeValue' .. Then ..
..

I was wondering why none of case statements were evaluating true. Finally, I came to know Column1 resulted in a null due to a left join and then null!='SomeValue' returned false. Syed Mehroz Alam

My Blog My Articles Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

modified on Friday, November 14, 2008 7:12 AM

[Jason Lepack LeppyR64]

Syed Mehroz Alam wrote:

We all know that null, when compared with anything, returns false. Even null=null evaluates to false. But I was amazed to see that null!=Value also returns false.

!= is still a comparison operator. Example: I have two things on my desk. One is a baseball. Is the other thing a baseball? Is the other thing not a baseball? You don't know, right? Now, I think it would have been more useful to have comparisons containing nulls return null, instead of false. But that is beside the point.

[PIEBALDconsult]

Syed Mehroz Alam wrote:

I was amazed

I wasn't.

[Scott Barbour]

Which is why you should use the isnull() function when you want to compare a nullable field to a another field or literal, and field is null (or field is not null) when you want to check for null, though I typically use isnull(field,'') <> '' as our users are wont to make fields empty strings.

I don't claim to be a know it all, for I know that I am not...

I usually have an answer though.

[Syed Mehroz Alam]

You are right, I also tackled it using IsNull but the original query wasn't written by me and it was hard to find the cause of error since the query was a complex one.

My Blog My Articles Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

[alejandrofuchs]

Null = Value does not evaluate to false, nor true. It's Unknown. So there are not only two values: True and False, but also Unknown From MSDN: A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

[Dag Oystein Johansen]

You're certainly right about how it actually does behave. But I don't really see why comparions with null ought to behave differently in SQL than in, say, C#. This whole design decision of using the is operator for comparison to null seems illogical and counterintuitive, but I presume there is some reason for it. If you see a good reason, would you care to elaborate?

[Dag Oystein Johansen]

Scott Barbour wrote:

I typically use isnull(field,'') <> '' as our users are wont to make fields empty strings.

If you do not want to represent two different things by null or empty string, why is the field nullable in the first place? In my view, using invariant representations is a virtue. (Of course, there may be some cases where null and empty string is different for some purposed but not others, and then I guess this technique might be useful.)

[Scott Barbour]

I didn't design the tables, I'm just tasked with working with them. If it has never had a value, it is null. If the value has been cleared, it is an empty string. Of course, using the isnull(field,'') is even more useful when you are looking for specific values when nulls are present. It has been my experience with MS SQL Server 2000 that queries behave oddly with nulls (such as excluding rows from the resultset.) especially when using the LIKE operator. Of course MSSQL 2000 has a number of quirks anyway. I've reworked a number of queries that just wouldn't return the correct data.

I don't claim to be a know it all, for I know that I am not...

I usually have an answer though.

[dave dolan]

something can BE null, but it can not be EQUAL TO null. Just like, for example, something (in real life) can be Big, but it cannot be Equal to Big.