In How Many Languages Is Null Not Equal to Anything Not Even Null

In how many languages is Null not equal to anything not even Null?

It's this way in SQL (as a logic language) because null means unknown/undefined.

However, in programming languages (like say, C++ or C#), a null pointer/reference is a specific value with a specific meaning -- nothing.

Two nothings are equivilent, but two unknowns are not. The confusion comes from the fact that the same name (null) is used for both concepts.

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.

In most SQL implementations, as opposed to standard programming languages, why doesn't x != null return true?

The null in most programming languages is considered "known", while 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 consequence, we need a special operator IS [NOT] NULL to test for it.

I'm guessing at least part of the motivation for such NULLs would be the behavior of foreign keys. When a child endpoint of a foreign key is NULL, it shouldn't match any parent, even if the parent is NULL (which is possible if parent is UNIQUE instead of primary key). Unfortunately, this brings many more gotchas than it solves and I personally think SQL should have gone the route of the "known" null and avoided this monkey business altogether.

Even E. F. Codd, inventor or relational model, later indicated that the traditional NULL is not optimal. But for historical reasons, we are pretty much stuck with it.

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

Difference between NULL in SQL and null in programming languages

In SQL, we're interested in storing facts in tables (a.k.a relations).

What Codd asked for was:

Rule 3: Systematic treatment of null values:

The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.

What we've ended up with is three-valued logic (as @zmbq stated). Why is it this way?

We have two items that we're trying to compare for equality. Are they equal? Well, it turns out that we don't (yet) know what item 1 is, and we don't (yet) know what item 2 is (both are NULL). They might be equal. They might be unequal. It would be equally wrong to answer the equality comparison with either TRUE or FALSE. So we answer UNKNOWN.

---

In other languages, null is usually used with pointers (or references in languages without pointers, but notably not C++), to indicate that the pointer does not, at this time, point to anything.

MySQL: Why can't I column_name not in (null, 'foo')?

The database structural query language SQL implements Three Valued Logic as a means of handling comparisons with NULL field content.

True
False
Unknown

The original intent of NULL in SQL was to represent missing data in a database, i.e. the assumption that an actual value exists, but that the value is not currently recorded in the database.

So comparison with UNKNOWN value gives indeterministic result which is evalauted to FALSE.

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 nullable types will not be equal in this case?

Using boolean logic with null nullable values in C# (and VB.Net) often times defies logic. I find the best way to make sense of it is to remember that "null is not a value". Because null is not a value you cannot do any operations on it. Hence things like "1 > null" and "1 < null" are both true.

Here is a detailed guide: http://msdn.microsoft.com/en-us/library/2cf62fcy.aspx

If you do want to treat null as a value then you could use the GetValueOrDefaultMethod() to equate null with the default value. For example

Assert.IsTrue(n1.GetValueOrDefault() <= n2.GetValueOrDefault());  // True

This is a bit verbose but it will get the job done.

Not equal query excludes NULL values

Nulls are not counted in comparison, if you want null values to be returned then you need to execute the following query:

SELECT * FROM MasterList WHERE Requested <> "Yes" OR Requested IS NULL;

---

Related Topics