operator NOT does not invert the result of a NULL comparison

Question

Please see these two statements:

select 'true'  where (1 = NULL) ; 

select 'true' where not (1 = NULL) ;

Both (!) of them return empty!

So that means that the expressions (1=NULL) and not (1=NULL) both return false! But how can that be? I thought the operator NOT(expression) will invert the result of the expression ?

According to Microsoft: "Comparing NULL to a non-NULL value always results in FALSE."

OK, but that would mean that the operator NOTmust invert the result. But why does it do this not then?

https://msdn.microsoft.com/en-us/library/ms188074.aspx

EDIT: I posted the wrong article. My quote above is from this article: https://msdn.microsoft.com/en-us/library/ms175118.aspx

It seems this article has an error, as stated below in the answers and comments.

Answer 1

So, T-SQL implements what is known as Three-Valued Logic. That means that each logical expression present in T-SQL code can evaluate to TRUE, FALSE OR NULL. Now SQL Server gives you 2 options to handle logical expressions with NULLs with the SET ANSI_NULL command. The default behaviour of SQL Server (SET ANSI_NULL ON) is that every logical comparison with NULL will return NULL. So the following expressions

NULL = NULL;
1 = NULL;
1 <> NULL;

will all evaluate to NULL. If for some reason you want the logical expression to return true or false even if they have NULL values in them, you have to turn ANSI_NULL OFF but it is not advisable.

Small edit: The only case that a logical expression including NULL will evaluate to something other than NULL is the following:

(NULL) OR (TRUE) = TRUE

So the following T-SQL code

SET ANSI_NULLS ON;
GO
IF ((NULL=NULL) OR (1=1))
   PRINT 'True';
GO

will actually print True.

Answer 2

See: SET ANSI_NULLS (Transact-SQL)

Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server 2016.

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

So if you use this and set it to OFF, your code will work as desired:

SET ANSI_NULLS OFF

select 'true'  where (1 = NULL) ; 

select 'true' where not (1 = NULL) ;

But as stated in the document, it will cause errors in the future.