SQL: Using NULL values vs. default values

Question

What are the pros and cons of using NULL values in SQL as opposed to default values?

PS. Many similar questions has been asked on here but none answer my question.

Answer 1

I don't know why you're even trying to compare these to cases. null means that some column is empty/has no value, while default value gives a column some value when we don't set it directly in query.

Maybe some example will be better explanation. Let's say we've member table. Each member has an ID and username. Optional he might has an e-mail address (but he doesn't have to). Also each member has a postCount column (which is increased every time user write a post). So e-mail column can have a null value (because e-mail is optional), while postCount column is NOT NULL but has default value 0 (because when we create a new member he doesn't have any posts).

Answer 2

Null values are not ... values!

Null means 'has no value' ... beside the database aspect, one important dimension of non valued variables or fields is that it is not possible to use '=' (or '>', '<'), when comparing variables.

Writting something like (VB):

if myFirstValue = mySecondValue 

will not return either True or False if one or both of the variables are non-valued. You will have to use a 'turnaround' such as:

if (isnull(myFirstValue) and isNull(mySecondValue)) or myFirstValue = mySecondValue 

The 'usual' code used in such circumstances is

if Nz(myFirstValue) = Nz(mySecondValue, defaultValue) 

Is not strictly correct, as non-valued variables will be considered as 'equal' to the 'defaultValue' value (usually Zero-length string).

In spite of this unpleasant behaviour, never never never turn on your default values to zero-length string (or '0's) without a valuable reason, and easing value comparison in code is not a valuable reason.