*solved* when to use NULL and when not to

[bcamp1973]

Ok, when creating fields in MySQL (or any SQL) i've never been clear on when it's best to set the field to NULL?  I know doing so saves space (right?) but what are the disadvantages to setting a field to null?  or are there any?  I've been searching the manual and other resources, but not finding a definitive answer :(

[wildteen88]

Well it depends on what the type of data the fields will hold. Say you are doing a questbook. You ahve three fields Name, Email and Website.

You obvisouly want to store the posters name and email address in the database so you make those fields not null. However for the webpage field you'll make that field NULL. As not everyone may have a website.

Thats one example of use of setting a row to null.

Null means whether the field can have a null value, meaning you are able to store nothing in that field. not null doesnt allow to store nothing in the field. So basically use NULL on a field when a field is not required to have a value when inserting data in to the database.

[fenway]

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.

[bcamp1973]

Great feedback!  Thanks to both of you!