Why Is Null Not Equal to Null False

why is null not equal to null false

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

Why NULL is not equal to anything is a false statement?

Almost any comparison to NULL returns NULL. This is because NULL has the semantics of "unknown value" rather than "missing".

When you have the comparison 9 <> NULL, then the returned value is NULL (for the comparison).

WHERE clauses treat NULL values as "false" so rows get filtered out. Similarly, CASE expressions treat NULL values as "false".

This is not always the case. CHECK constraints treat NULL values as "true", so the check constraint passes even when the values are NULL.

Why is `NOT(NULL=NULL)` false?

The three-valued logic (3VL) defines the logical operators as:

+---------+---------+---------+---------+---------+
| p       | q       | p OR q  | p AND q | p = q   |
+---------+---------+---------+---------+---------+
| True    | Unknown | True    | Unknown | Unknown |
| False   | Unknown | Unknown | False   | Unknown |
| Unknown | True    | True    | Unknown | Unknown |
| Unknown | False   | Unknown | False   | Unknown |
| Unknown | Unknown | Unknown | Unknown | Unknown |
+---------+---------+---------+---------+---------+

The NOT behavior has the following truth table:

+---------+---------+
| p       | NOT p   |
+---------+---------+
| True    | False   |
| False   | True    |
| Unknown | Unknown |
+---------+---------+

So, in the expression NOT(NULL = NULL), you get:

NULL = NULL -> Unknown
NOT(Unknown) -> Unknown

Your case condition always acts like not fulfilled because your expression evaluates to Unknown, i.e. neither true nor false.

For more information on the way SQL Server works regarding nulls, have a look at Why does NULL = NULL evaluate to false in SQL server

Not equal != operator on NULL

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

Why '0' does not equal null

A 'real' (strict) equivalence in PHP is === . If you use it you do enjoy the transitivity property you've mentioned.

But == isn't an exact equivalence in PHP. It employs converting operands to a common type first. As we can see, using false as the first operand causes the second one to be reduced toward a Boolean value. If, however, you compare a reference to a string, no such reduction happens that's why the values are treated to be different. Of course such an approach violates the transitivity property. But, again, === is a 'real' equality sign rather than ==.

Why does NULL = NULL evaluate to false in SQL server

Think of the null as "unknown" in that case (or "does not exist"). In either of those cases, you can't say that they are equal, because you don't know the value of either of them. So, null=null evaluates to not true (false or null, depending on your system), because you don't know the values to say that they ARE equal. This behavior is defined in the ANSI SQL-92 standard.

EDIT:
This depends on your ansi_nulls setting. if you have ANSI_NULLS off, this WILL evaluate to true. Run the following code for an example...

set ansi_nulls off

if null = null
    print 'true'
else
    print 'false'


set ansi_nulls ON

if null = null
    print 'true'
else
    print 'false'

Unexpected NOT EQUAL TO NULL comparison in MySQL

According to MySQL Reference Manual, section 3.3.4.6: Working with NULL values the following is why:

Because the result of any arithmetic comparison with NULL is also
NULL, you cannot obtain any meaningful results from such comparisons.

In MySQL, 0 or NULL means false and anything else means true. The
default truth value from a boolean operation is 1.

This means that NULL != 'BHR' will evaluate to NULL, which in turn will mean false to MySQL. In order for the query to work as you want, you have to append OR city_code IS NULL to your query.

Why (null == false) and (null == true) both return false?

This is because the Abstract Equality Comparison Algorithm requires that if Type(x) or Type(y) is a Boolean in the expression x == y then the Boolean value should be coerced to a number via ToNumber, which converts true to 1 and false to +0.

This means that any comparison of true == something or something == true results in 1 == something or something == 1 (replacing true and 1 with false and +0 for false).

The Null type does not compare as equal to either 1 or +0 (in fact, null is only comparable to undefined in the Abstract Equality Comparison Algorithm).

There is a detailed discussion of all of the different kinds of equality in JavaScript on MDN that is well worth looking at if you want to know more.

However, if you coerce null to a number it is coerced to +0 so +null == false actually returns true.

Why 0 and null are true and false with equal operator?

When you use > and <, null is converted to the number 0. 0 > 0 and 0 < 0 are both false (that's basic math). When you use == and ===, null is not converted. 0 is not equal to null and hence both are false as well.

More generally speaking: Operators are defined for specific data types and if you pass a value of a different data type that value will be converted to the expected data type first. > and < are defined for strings and numbers but not for null. Hence null is (eventually) converted to a number.

== are a little different ===. While == usually performs type conversion, it doesn't do that if you compare against null. That's simply how the algorithm works.

---

Related Topics