Could anyone explain the usage of NULL in database tables? What is it used for and how is it useful?
Add a comment |
8 Answers
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.
answered Jan 29, 2010 at 23:20
Lasse V. Karlsen
394k107 gold badges651 silver badges845 bronze badges
Comments
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"
2 Comments
Samuel Carrijo
Wow, I'm proud my answer being analogous to wikipedia answer =)
Walter Mitty
As I understand it, Codd stated that any relational database system has to have a systematic way of dealing with missing data. NULL is such a way, but I'm not sure that SQL NULL was the brainchild of Codd, even when Wikipedia says so.
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?
5 Comments
mthurlin
An empty string would fill that purpose just fine.
Samuel Carrijo
What if it is a number, a true/false, or what if an empty string makes sense in itself?
Frank Schwieterman
$$$-$$$-$$$$ is an interesting choice. I prefer NULL.
Samuel Carrijo
It would be completely weird - usually a bad practice... I suggested a number in a case there was a column like customer balance, or anything money related
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
Comments
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).
Comments
"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.
4 Comments
Aaronaught
And it's useful for generating endless rants from people who can offer neither a practical explanation for their angst nor a viable alternative, but damn well have an opinion and aren't about to let anyone forget it. -1.
Erwin Smout
The viable alternative is called "vertical decomposition". Another alternative is "How to handle missing information using specialization-by-constraint". And a third one is "multirelations". And I consider the example of '(x == y) != !(x == y)' to be a quite god damn practical explanation.
Erwin Smout
Sorry, should have been '(x == y) == !(x == y)'
Erwin Smout
But then again, if X can be equal to NOT(X), then the word 'NOT' becomes pretty meaningless, of course, and any symbol denoting it with it.
Please check this question.
"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?
Comments
It Simply signifying "missing values" or the "values which are not known" at the time of inserting into the database for the particular records.
