Should I use NULL or an empty string to represent

Question

Null or empty string -- is one better than the other to represent no data in a table column? (I specifically use MySQL, but I'm thinking this is system-independent.) Are there major advantages/disadvantages to using one over the other, or is it simply programmer preference?

Answer 1

Here are a couple links from the MySQL site:

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

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

I did read once, that a NULL value is 2 bits, where as an empty string is only 1 bit. 99% of the time this won't make any difference, but in a very large table when it doesn't matter if NULL or '', then it might be better to use '' if this is true.

Answer 2

I strongly disagree with everyone who says to unconditionally use NULL. Allowing a column to be NULL introduces an additional state that you wouldn't have if you set the column up as NOT NULL. Do not do this if you don't need the additional state. That is, if you can't come up with a difference between the meaning of empty string and the meaning of null, then set the column up as NOT NULL and use empty string to represent empty. Representing the same thing in two different ways is a bad idea.

Most of the people who told you to use NULL also gave an example where NULL would mean something different than empty string. And in those examples, they are right.

Most of the time, however, NULL is a needless extra state that just forces programmers to have to handle more cases. As others have mentioned, Oracle does not allow this extra state to exist because it treats NULL and empty string as the same thing (it is impossible to store an empty string in a column that does not allow null in Oracle).

Answer 3

Consider why there is no data in the column. Does it mean the table design is sloppy? Despite not liking nulls, there are occasions when they are appropriate (or, appropriate enough), and the system won't usually die. Just never allow nulls in anything that is a candidate key (primary or alternative key).

Answer 4

Always use NULL. Consider the difference between "I don't know what this person's phone number is" (NULL) and "this person left it blank" (blank).