In most SQL implementations, unlike standard programming languages, why does x! = Null not return true?

Question

Suppose that x - is a variable, which as an example has any value other than null , say 4. What should return the following expression?

 x != null 

In any programming language I have ever worked with (C #, Javascript, PHP, Python), this expression or equivalent expression in this language evaluates to true .

SQL implementations, on the other hand, all seem to handle this completely differently. If one or both operands of the inequality operator is null , then null or False returned. This is basically the opposite of the behavior that most programming languages ​​use, and for me it is extremely uninteresting.

Why is this behavior in SQL? What is relationaly database logic that makes null behave differently than in general-purpose programming?

Answer 1

Zero in most programming languages ​​is considered "known", and NULL in SQL is considered "unknown".

• So, X == null compares X with a known value, and the result is known (true or false).
• But X = NULL compares X with an unknown value, and the result is unknown (i.e. NULL, again). As a result, we need a special IS [NOT] NULL statement to test it.

I assume that at least part of the motivation for such NULLs is the behavior of foreign keys. When the child endpoint of the foreign key is NULL, it must not match any parent, even if the parent is NULL (which is possible if the parent is a UNIQUE instead of the primary key). Unfortunately, this brings a lot more gotchas than it solves, and I personally believe that SQL had to go the route of "known" zero and avoid this business of monkeys in general.

Even EF Codd, the inventor or relational model, later pointed out that traditional NULL is not optimal. But for historical reasons, we are pretty much stuck with this.

Answer 2

the reason is that the concept of equality does not apply to zero. it is not logically true to say that this null is equal to or not equal to this other null value.

so that everything is fine for a theoretical reason, but for convenience, why sql doesn't let you say (x! = null)?

Well, the reason is that sometimes you want to handle nulls differently. if I say (e.g. columnA = columnB), should it return true if both columns are null? if I say (columnA! = columnB) - should it give the same result when column A is "a" and column B is null, and when column A is "a" and column B is "b"?

the people who made sql decided that the difference was important, and therefore they wrote it to treat the two cases differently.

on the wikipedia page it has a pretty decent post - http://en.wikipedia.org/wiki/Null_%28SQL%29

Answer 3

Well in SQL engines you usually don't use the "=" operator, but the "IS" operator, which makes it more intuitive.

 SELECT 4 IS NULL FROM dual; > 0 SELECT 4 IS NOT NULL FROM dual; > 1 

NULL does not support a null pointer; this is not at all the same concept. sql NULL - this is I do not know the value flag, this is not the "no pointer" flag. You just do not have to compare them, they cannot be used the same way. It's pretty unintuitive, you're right, they should have called it differently.

Answer 4

In the SQL NULL stands for "value unknown".

If you say x! = NULL, you say: "this is the value of x, not equal to the unknown value." Well, since we don’t know what an unknown value is, we don’t know if x is equal to it or not. Therefore, the answer is "I do not know."

Similarly:

 x = NULL OR 1=2 -- Unknown. 1=2 is not true, but we don't know about x=NULL x = NULL OR 1=1 -- True. We know that at least 1=1 is true, so the OR is fulfulled regardless. x = NULL AND 1=1 -- Unknown. We want them both to be true to fulful the AND x = NULL AND 1=2 -- False. We know 1=2 is false, so the AND is not fulfilled regardless. 

Besides

 -- Neither statement will select rows where x is null select x from T where x = 1 select x from T where x != 1 

The only way to check for zero is to ask the question "is it true that we do not know what the value of x is". It has a yes or no answer and uses the IS keyword.

If you want zeros to be treated as zero or another value, you can use the COALESCE or ISNULL .

 COALESCE(NULL, 1) -- 1 COALESCE(NULL, NULL, 1) -- Also 1 COALESCE(x, y, z, 0) -- x, unless it is null, then y, unless it is null, then z, unless it is null in which case 0.