Not equal <> != operator on NULL

Question

Could someone please explain the following behavior in SQL?

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) 

Answer 1

<> 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.

Answer 2

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.