Problem
Could you please explain the following SQL behavior?
SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results)
SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results)
SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)
Asked by Maxim Gershkovich
Solution #1
!= is the equivalent of Standard SQL-92. Both look for values, which NULL isn’t — NULL is only a placeholder for the lack of a value.
As a result, you can only use IS NULL/IS NOT NULL as a predicate in these cases.
This is not a SQL Server-specific behavior. All SQL dialects that follow the standards work in the same way.
Note that IS NOT NULL is used to check if your value is not null, whereas > ‘YOUR VALUE’ is used to compare if your value is not null. I can’t say whether my value is equal to NULL or not, but I can state whether it is NULL or NOT NULL. If my value isn’t NULL, I can make a comparison.
Answered by OMG Ponies
Solution #2
The scalar value operators cannot compare NULL since it has no value.
In other words, no value can ever be equal to (or not equal to) NULL because NULL has no value.
As a result, SQL provides particular predicates for dealing with NULL: IS NULL and IS NOT NULL.
Answered by Barry Brown
Solution #3
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 receive a variety of outcomes.
SET ANSI NULLS OFF appears to be deprecated in the near future…
Answered by Cade Roux
Solution #4
I just don’t see why nulls shouldn’t be compared to other values or nulls, because we can plainly compare them and state whether they’re the same or not in our context. It’s amusing. We must continually bother with it just because of certain logical consequences and consistency. Make it more practical and leave it to philosophers and scientists to determine whether or not it is coherent and holds “universal logic.”:) Someone might say it’s because of indexes or something else, but I doubt those things couldn’t be changed to support nulls in the same way that they support values. It’s like comparing two empty glasses, one of which is a wine glass and the other a beer glass.
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 have a clue what this code will return? It returns 0 with or without NOT. That is ineffective and perplexing to me. In C#, everything works as it should; comparison procedures yield value, and theoretically, this should as well, because there would be nothing to compare (except nothing:)). They just “stated” that anything that compares to null “returns” 0, which leads to a slew of workarounds and difficulties.
This is the code that led me to this page:
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…
Answered by Hrvoje Batrnek
Solution #5
We use
SELECT * FROM MyTable WHERE ISNULL(MyColumn, ' ') = ' ';
to return all rows in which MyColumn is NULL or an empty string. To many “end users,” the NULL vs. empty string debate is a matter of misunderstanding rather than a necessary distinction.
Answered by Jeff Mergler
Post is based on https://stackoverflow.com/questions/5658457/not-equal-operator-on-null