What are database NULL fields for?

Question

Could anyone explain the usage of NULL in database tables? What is it used for and how is it useful?

Thanks, Boda Cydo.

Answer 1

From Wikipedia:

Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information"

Answer 2

It's something like a column filled with "I don't know", or "doesn't matter". For instance, in a table of customers, if they don't fill their phone number, what do you put in the phone number field?

Answer 3

The represent the lack of any value.

NULL can be used to store optional data in a table. Say you have a Car table. Not all cars have trunks...so their TrunkSpace column could be NULL

Answer 4

It is useful to mark "unknown" or missing values.

For instance, if you're storing people, and one of the fields is their birthday, you could allow it to be NULL to signify "We don't know when he was born".

This is in contrast to having non-nullable fields where, for the same type of meaning, you would need to designate a "magic value" which has the same meaning.

For instance, what date should you store in that birthday field to mean "we don't know when he was born"? Should 1970-01-01 be that magic value? What if we suddenly need to store a person that was born on that day?

Now, having said that, NULL is one of the harder issues to handle in database design and engines, since it is a propagating property of a value.

For instance, what is the result of the following:

SELECT *
FROM people
WHERE birthday > #2000-01-01#

SELECT *
FROM people
WHERE NOT (birthday > #2000-01-01#)

(note, the above date syntax might not be legal in any database engine, don't get hung up on it)

If you have lots of people with an "unknown" birthday, ie. NULL, they won't appear in either of the results.

Since in the above two queries, the first one says "all people with criteria X", and the second is "all people without criteria X", you would think that together, the results of those two queries would produce all the people in the database.

However, the queries actually say "all people with a known and definite criteria X."

This is similar to asking someone "am I older than 40?" upon which the other person says "I don't know". If you then proceed to ask "am I younger than 41?", the answer will be the same, he still doesn't know.

Also, any mathematics or comparisons will produce NULL as well.

For instance, what is:

SELECT 10 + NULL AS X

the result is NULL because "10 + unknown" is still unknown.

Answer 5

I like JRL/Wikipedia's answer. It's sometimes desirable to fill a column with NULL when you don't want to default it to an actual value. I mean, there are times when an empty string could be used instead of a NULL when you're working with a varchar. But what about a bit or date type? If a bit column is used then defaulting it to 0 or 1 may not really be sufficient. And what should the default date be for a date type?

There's one more reason to use NULLS in some systems. SQL Server 2008 introduced the concept of sparse columns that have optimized storage for NULL values. They are best to use when you have a column that is likely to be mostly empty (or mostly NULL to be precise).

Answer 6

"What is it used for and how is it useful?"

It is used for "solving" (and please, note the quotation marks) the problem of coping with information that is missing.

It is useful for creating shit loads of both software design problems and software implementation problems (the most blatant one of which can be summarized as '(x == y) == !(x == y)'), or in other words : it is useful for creating job security for the shit load of programmers who claim that nulls are inevitable.

Answer 7

Please check this question asked a few days ago.

"Null" means basically "That has no value", but can be also mean "Which value is unknown", thus making its intepretation sometimes ambiguous. Think of a person table, with both date of birth and a driving license n# field. If the date of birth field has no value, it clearly means that the date is unknown (everybody is born one of these days...). Now, if the driving license field does not hold any value, does it mean that the person doesn't have a driving license, or doesit mean that its n# is not known?