Why does NULL = NULL evaluate to false in SQL server

Question

In SQL server if you have  nullParam=NULL in a where clause, it always evaluates to false. This is counterintuitive and has caused me many errors. I do understand the IS NULL and IS NOT NULL keywords are the correct way to do it. But why does SQL server behave this way?

Answer 1

You can think of the null as "unknown" in that case (or "does not exist"). In either of the cases, you can't say that they are equal, because you don't know the value of either of them. 

Therefore, null=null evaluates to not true (false or null, depending on the system), because you don't know the values to say that they ARE equal. This method is defined in the ANSI SQL-92 standard.

Note: It depends on your ansi_nulls setting. If you have ANSI_NULLS off, then it will evaluate to true. 

As an example, you can run the below code:

set ansi_nulls off

if null = null

    print 'true'

else

    print 'false'

set ansi_nulls ON

if null = null

    print 'true'

else

    print 'false'