Home Software Consultant

The NULL conundrum

2014-03-14T12:34:04+00:00

So, you have a text field, named 'Contact_Person' defined in a table. This field is not required, and you allow null. Why not define a default value as empty string? It seems to me using an empty string is convenient. I don't have to check for 'null' in the client application. What is NULL? According to this wikipedia page,

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

Now, NULL represents UNKNOWN. Sql actually uses three-valued logic. This article gives some examples, and writes

Accepts TRUE = Reject both FALSE and UNKNOWN Rejects FALSE = Accepts both TRUE and UNKNOWN

On stackexchange, the argument made is logical that

I would say that NULL is the correct choice for "no email address". There are many "invalid" email addresses, and '' (empty string) is just one. For example "foo" is not a valid email address, "a@b@c" is not valid and so on. So just because '' is not a valid email address is no reason to use it as the "no email address" value

Few more interesting questions - NULL = NULL = FALSE -

NULL is a textual representation of an unknown value. If you have two unknown values, you can't conclusively state anything about their equality

Since it's December, let's use a seasonal example. I have two presents under the tree. Now, you tell me if I got two of the same thing or not. They can be different or they can be equal, you don't know until one open both presents. Who knows? You invited two people that don't know each other and both have done to you the same gift - rare, but not impossible §. So the question: are these two UNKNOWN presents the same (equal, =)? The correct answer is: UNKNOWN (i.e. NULL).

SQL does not any good forcing one to interpret the reflexive property of equality, which state that: for any x, x = x §§ (in plain English: whatever the universe of discourse, a "thing" is always equal to itself).

Why does column = NULL return no rows ?