Difference between '<>' and 'Is NOT' in TSQL?

Question

I don't get it.

I had this expression and it didn't work.

IF @LocationName <> NULL AND
@Latitude <> NULL AND
@Longitude <> NULL AND
@Zoom <> NULL AND
@MapTypeId <> NULL
BEGIN
... 
END

I changed it to this expression.

IF @LocationName Is NOT NULL  AND
@Latitude Is NOT NULL AND
@Longitude Is NOT NULL AND
@Zoom Is NOT NULL AND
@MapTypeId Is NOT NULL
BEGIN
... 
END

And it worked...

WTH?

Answer 1

The behavior of <> can be modified by setting SET ANSI_NULLS.

EDIT: However, it is not advisable to do so, as the text in the page above suggests.

Answer 2

As expected.

Every comparison against NULL is always UNKNOWN (which equates to false).

Also known as Three-valued logic (Wikipedia) and MSDN/SQL Server

Answer 3

You can't use comparison operators when dealing with NULL values. NULL isn't really any value so you can't compare it - when you try to compare it will return UNKOWN.

You have to use IS NULL or IS NOT NULL

IS [NOT] NULL

Null Values

Answer 4

That's ternary logic. It is one of main SQL features and you should always remember it.

Answer 5

You can't check if something is equal (or not) to NULL, as NULL is of unknown value and type.

You have to check if it IS NULL or IS NOT NULL