why is null not equal to null false

Solution 1:

Because that behavior follows established ternary logic where NULL is considered an unknown value.

If you think of NULL as unknown, it becomes much more intuitive:

Is unknown a equal to unknown b? There's no way to know, so: unknown.

Solution 2:

relational expressions involving NULL actually yield NULL again

edit

here, <> stands for arbitrary binary operator, NULL is the SQL placeholder, and value is any value (NULL is not a value):

NULL <> value -> NULL
NULL <> NULL -> NULL

the logic is: NULL means "no value" or "unknown value", and thus any comparison with any actual value makes no sense.

is X = 42 true, false, or unknown, given that you don't know what value (if any) X holds? SQL says it's unknown. is X = Y true, false, or unknown, given that both are unknown? SQL says the result is unknown. and it says so for any binary relational operation, which is only logical (even if having NULLs in the model is not in the first place).

SQL also provides two unary postfix operators, IS NULL and IS NOT NULL, these return TRUE or FALSE according to their operand.

NULL IS NULL -> TRUE
NULL IS NOT NULL -> FALSE

Solution 3:

All comparisons involving null are undefined, and evaluate to false. This idea, which is what prevents null being evaluated as equivalent to null, also prevents null being evaluated as NOT equivalent to null.

Solution 4:

The short answer is... NULLs are weird, they don't really behave like you'd expect.

Here's a great paper on how NULLs work in SQL. I think it will help improve your understanding of the topic. I think the sections on handling null values in expressions will be especially useful for you.

http://www.oracle.com/technology/oramag/oracle/05-jul/o45sql.html

Solution 5:

The default (ANSI) behaviour of nulls within an expression will result in a null (there are enough other answers with the cases of that).

There are however some edge cases and caveats that I would place when dealing with MS Sql Server that are not being listed.

Nulls within a statement that is grouping values together will be considered equal and be grouped together.
Null values within a statement that is ordering them will be considered equal.
Null values selected within a statement that is using distinct will be considered equal when evaluating the distinct aspect of the query

It is possible in SQL Server to override the expression logic regarding the specific Null = Null test, using the SET ANSI_NULLS OFF, which will then give you equality between null values - this is not a recommended move, but does exist.

SET ANSI_NULLS OFF

select result =
    case
        when  null=null then 'eq' 
        else 'ne'
    end

SET ANSI_NULLS ON

select result =
    case
        when  null=null then 'eq' 
        else 'ne'
    end