Is better use an empty value as a '' or as NULL?

Question

Database schema

I have this fields:

• title (string)
• subtitle
• description (string)

Is better set the default value as an empty string '' or a NULL?

For better reading/writing and size-storage performance

Answer 1

Expert opinion: Avoid NULL

Dr. Chris (C.J.) Date, who partnered with the creator/discoverer of the Relational Data Model (Dr. Codd) clearly says, "No, you should not use NULLs.". Read his book A Guide To The SQL Standard for much discussion.

He, along with other experts, argue that for a variety of both theoretical and practical issues, NULLs bring too much risk, confusion, and problems to make them worth while.

Solution:

• Add a constraint on every column of NOT NULL.
• Where it makes sense, add a default value to each column, DEFAULT. For a text type column, the default value may be an empty string (''). Or the default may be a certain wording you arbitrarily choose and use consistently such as 'EMPTY' or 'NOT-AVAILABLE' depending on your needs. On some columns you may not want a default, meaning you want to reject the record's insertion or update if the user/app did not provide a value.

Exception To The Rule

The above is a good rule, and I follow it habitually. However, every rule has exceptions. In very rare cases, I've made exceptions with allowing NULLs.

Example Exception: In Postgres when using the XML data type in table of millions of rows. I need to repeatedly search for rows with no value yet recorded (missing values). I cannot store an empty string as Postgres enforces a rule that only valid XML be stored, and an empty string is not a valid XML document. So I permit NULLs in that XML column.

You might think I could store a minimal XML document containing no data. But I do not know how to index on that efficiently, to differentiate between rows with recorded data and those without. I can create an index on NULL.

Answer 2

You need to decide whether values of 'null' and empty string mean in your application something different, or maybe both of them just mean 'no data'. If the latter is the case, then it is generally just a matter of preference, but you'd have to be consequent - try not to mix 'null' values and empty ones in given field.

Generally 'null' gives better notion of "no data", but compared to empty string it is somewhat more cumbersome to use in an application. But then using empty strings instead of nulls may be seen as premature optimalization, and will make it impossible at some time in the future to introduce functionality which needs to distinguish between nulls and empty strings.

On the other hand there are DBMSs which do not store nulls in string columns, just empty strings. I'd go with nulls, but with well established and documented contract (i.e. "This fields never contains null, empty title means no title", enforced with NOT NULL constraint on column) which is followed consistently you could take whichever approach you like.

If you are concerned with performance, you'd need to read documentation of DBMS you are using and make some tests on your own. If you expect that empty values are very frequent, you could check, whether 'sparse columns' would be of any help - some DBMSs introduce these as means of efficient storage of frequently occurring null values, but they usually have some drawbacks, like general (usually not big) performance penalty on retrieving non-null value, or something like this.

And, of course, you have to take into account what clients expect. But when you create database which is not yet accessed by clients, it's up to you to decide and document it appropriately.

Answer 3

It depends. Do you know the value is empty? Example: Person is known to have no middle initial.

Or do you just plain not know? Example: you received a form where the field "Middle Initial" has been left blank.

Answer 4

Most is already said about this, but I think there is one more thing to consider if you finally think it's 50/50 between using NULL or "" as "no value" (simply spoken).

In MySQL value NULL is not "caught" if you have a negative condition on a column. Eg.

where column != 'text'

will only return rows where "column" doesn't have the value "text" but won't find rows where "column" is NULL, if you want to find also these rows you must use:

where column != 'text' OR column IS NULL

I myself still prefer using NULL and changing empty strings to nil before save, I think it's better to know that "empty value" always is NULL in the database.

On the other hand, there can be some situations where you want to use the difference between "no value" (NULL) and "empty value" (""). But I have never had that situation in some application - yet.

Answer 5

This is not strictly applicable to your case, but I'll mention it for completeness: NULL foreign keys are not enforced.

If you had a field foreign_id that is a foreign key referencing some other table, it would be enforced only when foreign_id contains a non-NULL value.

BTW, Oracle stores empty string as NULL. VARCHAR2 is guaranteed to continue to behave that way, while VARCHAR might (one day) be changed to conform to the SQL standard and make the distinction between empty string and NULL. Other DBMSes (that I know of) do make that distinction.

Answer 6

Use Null values only when the data is not known or not applicable..In all other cases use ""(empty value) as a special consideration is needed while writing queries for data involving NULL values which is often difficult..

Answer 7

You should always use NULL to denote that the column doesn't have a value as even an empty string is a value.

Answer 8

The usual contract is:

• NULL means "no information available".
• '' means "there is information available. It's just empty."

Beyond this point there is much philosophical discussion since the invention of NULL in any language, not just SQL.

The only technical point here is: In PostgreSQL NULL can be stored more efficiently than a string of length zero. If that really matters in your case ... we cannot know.