<> 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.
<> 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.
In several languages NULL is handled differently: Most people know about two-valued logic where true and false are the only comparable values in boolean expressions (even is false is defined as 0 and true as anything else).
In Standard SQL you have to think about three-valued logic. NULL is not treated as a real value, you could rather call it "unknown". So if the value is unknown it is not clear if in your case state is 0, 1, or anything else. So NULL != 1 results to NULL again.
This concludes that whereever you filter something that may be NULL, you have to treat NULL values by yourself. Note that the syntax is different as well: NULL values can only be compare with x IS NULL instead of x = NULL. See Wikipedia for a truth table showing the results of logic operations.
Yest it's normal, you can maybe put a database settings to fixed that
But you could modify your code and do something like that :
SELECT * FROM STATUS WHERE STATE != '1' OR STATE != '2' or STATE is null;
Look at this for more info : http://www.w3schools.com/sql/sql_null_values.asp
Take a look at PSOUG's notes on NULL. As Fabricio Araujo hinted, NULL is not really a value like the number 4 or string 'bacon strips'. In fact, NULL is untyped in the SQL language, which is why you cannot validly use it in an equality comparison. You need the special IS [NOT] NULL syntax to check if a value is NULL or not.
In SQL Server, we have an connection setting to get =NULL to behave equally to IS NULL. But in latest versions is not recommended anymore - it's even marked as deprecated.
The recommended is the SQL Standard way - the IS [NOT] NULL operator.
(And I will not start an war whether 'NULL is a value or a status' here)... hehehe
You can simplify the condition to just:
WHERE id != 0
because comparisions with NULL (using both = or != operators) always evaluates to NULL (which is treated in SQL as "false" in conditions),
therefore NULL != 0 always evaluates to FALSE and you can skip this part in this condition
WHRE id != 0 AND id IS NOT NULL
Braiam's answer Where nvl(Id,0)<>0, although correct, will prevent database from using an index on id column, and this could have bad impact on the performce.
Shouldn't it be
WHERE id IS NOT NULL
AND id != 0
In Oracle, there is no difference between an empty string and NULL.
That is blatant disregard for the SQL standard, but there you go ...
In addition to that, you cannot compare against NULL (or not NULL) with the "normal" operators: "col1 = null" will not work, "col1 = '' " will not work, "col1 != null" will not work, you have to use "is null".
So, no, you cannot make this work any other way then "col 1 is null" or some variation on that (such as using nvl).
I think that the solution that you posted is one of best options.
Regarding to performance, in my opinion it is not a big difference in this case, if the clause already have a != comparison usually the optimizer won't use an index in that column, because the selectivity is not enough, so the more discriminating filter will be the other side of the "and" condition.
If you ask me, I won't use an empty string as a null, but may be is just a personal preference.