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

Question

I am sure I fail to understand (and may be accept) this, but can someone please bring comfort to my sense?

SELECT IF(NULL != 9, 'TRUE', 'FALSE')

*Am sorry if this is an inappropriate place for this, but StackOverFlow is the first thing came into my mind for an enlightment.

Does this answer your question? [MySQL comparison with null value](https://stackoverflow.com/questions/9608639/mysql-comparison-with-null-value) — nbk, Feb 01 '20 at 13:28

score 3 · Accepted Answer · answered Feb 01 '20 at 13:45

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.

slaakso · Answer 2 · 2020-02-01T17:12:32.797

1

A NULL in SQL means that it does not have a value. Any value. It is not less, equal or greater than any other value.

So:

NULL = 9  => null
NULL < 9  => null
NULL <= 9  => null
NULL > 9  => null
NULL >= 9  => null
NULL != 9  => null
NULL = NULL  => null

What is TRUE is value is NULL when the value is NULL.

edited Feb 01 '20 at 17:12

answered Feb 01 '20 at 13:29

slaakso

8,331
2
16
27

What I fail to understand is, if NULL = UNKNOWN = never equals to ANYTHING, then WHY NULL is NOT equal to ANYTHING would come up as FALSE! – Broken Arrow Feb 01 '20 at 13:52
1

As said, `NULL` does not have any kind of value, i.e the value is undefined. So whatever value your compare it to, the result is `FALSE`. You cannot say a column is equal or not equal to something, if it simply is undefined. – slaakso Feb 01 '20 at 14:05
NULL _might_ equal anything. – Rick James Feb 07 '20 at 06:49
You might want to add `<=>` into your list. – Rick James Feb 07 '20 at 06:50
1

@RickJames. The SQL92 stadard definition for NULL is: "A null value is an implementation-dependent special value that is distinct from all non-null values of the associated data type.". So it cannot be equal to anything. – slaakso Feb 07 '20 at 08:36

score 0 · Answer 3 · answered Feb 01 '20 at 16:19

If you are dealing with NULL in the database, then you should consider the expressions as "trinary logic". That is, a boolean value does not evaluate to two values anymore (as in binary), but to three:

True.
False.
Null: The SQL Standard is not clear about its definition, but it may mean the total absence of value, that the value is unknown, or it cannot be determined.

Now, take notice the CHECK constraint and the WHERE clause behave differently regarding null values. Consider the following example:

create table t (
  a int check (a <> 5)
);

insert into t (a) values (1), (2), (null);

The WHERE clause

A WHERE clause accepts a row if the predicate (the expression that represents the condition) is "true"; it rejects the row if the predicate evaluates to "false" or to "null".

select * from t where a = 1;

Result:

a
-
1

As you see, "null" was excluded from the result set since the predicate a = 1 evaluated to "null".

The CHECK Constraint

The CHECK constraint works a little bit different than WHERE. The predicate is rejected when it evaluates to "false" only; it's accepted when it evaluates to "true" and also "null".

That's why it inserted all three rows, including the "null" value.

select * from t;

Result:

a
----
   1
   2
null

For the "null" value, the predicate a <> 5 evaluated to "null" so it was accepted, and the row was inserted.

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

3 Answers3