why is null not equal to null false
database, null, nullable, sql
Solution
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`
Problem
I was reading this article: Get null == null in SQL And the consensus is that when trying to test equality between two (nullable) sql columns, the right approach is: ``` where ((A=B) OR (A IS NULL AND B IS NULL)) ``` When A and B are NULL, (A=B) still returns FALSE, since NULL is not equal to NULL. That is why the extra check is required. What about when testing inequalities? Following from the above discussion, it made me think that to test inequality I would need to do something like: ``` WHERE ((A <> B) OR (A IS NOT NULL AND B IS NULL) OR (A IS NULL AND B IS NOT NULL)) ``` However, I noticed that that is not necessary (at least not on informix 11.5), and I can just do: ``` where (A<>B) ``` If A and B are NULL, this returns FALSE. If NULL is not equal to NULL, then shouldn't this return TRUE? EDIT These are all good answers, but I think my question was a little vague. Allow me to rephrase: Given that either A or B can be NULL, is it enough to check their inequality with ``` where (A<>B) ``` Or do I need to explicitly check it like this: ``` WHERE ((A <> B) OR (A IS NOT NULL AND B IS NULL) OR (A IS NULL AND B IS NOT NULL)) ``` REFER to this thread for the answer to this question.