Not equal <> != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not null value, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.

NULL has no value, and so cannot be compared using the scalar value operators.

In other words, no value can ever be equal to (or not equal to) NULL because NULL has no value.

Hence, SQL has special IS NULL and IS NOT NULL predicates for dealing with NULL.

Note that this behavior is the default (ANSI) behavior.

If you:

 SET ANSI_NULLS OFF

http://msdn.microsoft.com/en-us/library/ms188048.aspx

You'll get different results.

SET ANSI_NULLS OFF will apparently be going away in the future...

I just don't see the functional and seamless reason for nulls not to be comparable to other values or other nulls, cause we can clearly compare it and say they are the same or not in our context. It's funny. Just because of some logical conclusions and consistency we need to bother constantly with it. It's not functional, make it more functional and leave it to philosophers and scientists to conclude if it's consistent or not and does it hold "universal logic". :) Someone may say that it's because of indexes or something else, I doubt that those things couldn't be made to support nulls same as values. It's same as comparing two empty glasses, one is vine glass and other is beer glass, we are not comparing the types of objects but values they contain, same as you could compare int and varchar, with null it's even easier, it's nothing and what two nothingness have in common, they are the same, clearly comparable by me and by everyone else that write sql, because we are constantly breaking that logic by comparing them in weird ways because of some ANSI standards. Why not use computer power to do it for us and I doubt it would slow things down if everything related is constructed with that in mind. "It's not null it's nothing", it's not apple it's apfel, come on... Functionally is your friend and there is also logic here. In the end only thing that matter is functionality and does using nulls in that way brings more or less functionality and ease of use. Is it more useful?

Consider this code:

SELECT CASE WHEN NOT (1 = null or (1 is null and null is null)) THEN 1 ELSE 0 end

How many of you knows what will this code return? With or without NOT it returns 0. To me that is not functional and it's confusing. In c# it's all as it should be, comparison operations return value, logically this too produces value, because if it didn't there is nothing to compare (except. nothing :) ). They just "said": anything compared to null "returns" 0 and that creates many workarounds and headaches.

This is the code that brought me here:

where a != b OR (a is null and b IS not null) OR (a IS not null and b IS null)

I just need to compare if two fields (in where) have different values, I could use function, but...

We use

SELECT * FROM MyTable WHERE ISNULL(MyColumn, ' ') = ' ';

to return all rows where MyColumn is NULL or all rows where MyColumn is an empty string. To many an "end user", the NULL vs. empty string issue is a distinction without a need and point of confusion.