[SOLVED] NULL

[asmith]

i've seen a lot of times people define their table fields with NOT NULL . 

is that for security ? cause i am working with mysql , i've never define that ( i work locally), and never feel the use of it , what matters if a field stays NULL ?

security reasons ?

[toplay]

No it's not for security reasons. Read:

 

http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html

 

I personally don't like to deal with NULL values and having to use special NULL condition statements, etc.

 

 

 

[roopurt18]

Since NULL is not equal to NULL, it also disobeys UNIQUE indexes in most of the engine types.

 

Declaring a column as able to contain NULL will also use a small amount of extra storage for that column.

[fenway]

I've talked about the value of NULL a few times over the years... maybe I should sticky a detailed discussion of this...

 

From a performance standpoint, yes, NULL requires an extra bit, affects JOINs, has implications for indexes, etc., so there's no need to use it when it's not necessary.  That being said, the only time you actually need it is when you need to know the difference between blank and NULL. 

 

For example, it's silly to have a NULLable gender column, since everyone has a gender, so blank (empty column) is just as good as male/female.  However, if your storing, say, how many children someone has, you need to be able to distinguish between "0" (no children) and no answer (unknown #).

 

Hope that makes sense.

 

If you ask someone a question like "how many siblings do you have?", zero is a perfectly valid answer.  However, if someone simply doesn't answer the question, the "blank" value would be zero as well.  With a NULL value, you'd have no way to differentiate between those who simply didn't complete this question (and may have 3 siblings -- i.e. an _unknown_ number) vs. those who did answer but were an only child.

 

Ok, it's like this: you need to use NULL whenever you need to be able to tell the difference between "no value specified" and "none/zero/nothing".  For example, if you were storing the answer to the question "How many brothers do you have?", and the question was optional, how would you store these two possible scenarios? You can't simply store the number, because does zero mean the question wasn't answered or the user actually has _no_ brothers?