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

Null. An empty string isn't "no data", it's data that happens to be empty.

Answer 2

There is one important exception. Bill Karwin stated "CONCAT(NULL, 'foo') yields NULL" which is true for most RDBMSs but NOT for Oracle.

As suggested by James Curran above, Oracle chose this rather critical juncture to depart from standard SQL by treating NULLs and empty strings exactly the same. Worse than just treating them the same, however, it can actually corrupt the meaning of a NULL value by returning something other than NULL when concatenating.

Specifically, in oracle CONCAT(NULL, 'foo') yields 'foo'. Thanks Oracle, I've now lost my nulls which may not matter to you but sure makes a difference when the data is passed to other RDBMSs for further processing.

Answer 3

Use the right tool for the job. NULL can signify that no value was provided (yet) or it can signify that no value is applicable.

But an empty string is information too. It can signify that a value is applicable, and was given, but it happens to be an empty string.

Allowing a column to contain both NULL and '' gives you the opportunity to distinguish between these cases. In any case, it's not good to use one to signify the other.

Be aware that in string concatenation, anything combined with NULL yields NULL. For example: CONCAT(NULL, 'foo') yields NULL. Learn to use the COALESCE() function if you want to convert NULL to some default value in an SQL expression.

Answer 4

Null is better "" actually represents data and it wont register the same in your code

Answer 5

Most of the time null is better. There are probably some situations where it makes little difference, but they are few. Just remember when you query that field = '' is not the same as field is null (in MySQL, at least).

Answer 6

A "no data" value in a column should be represented by a default value. Remember that NULL signifies an unknown value, that is, the column can have a value or not but you don't know it as of this time.

In a loan application system for example, a NULL value on the Driver's License Number field means that the applicant or the loan processor didn't input the driver's license number. The NULL value doesn't automatically mean the applicant doesn't have a license. He may or may not have a license, you just don't know it, that's why it's NULL.

The ambiguity lies for string columns. A numeric column obviously contains a zero if there is no value. How can you represent a no value string? In the example above, for applicants with no driver's license, you can assign an arbitrary default value such as "none" or better yet an empty string. Just ensure that you use the default empty value in your other tables for consistency.

On the issue of not using NULLs as a principle, there are instances where they are in fact essential. As someone who works with statistics extensively, it is common for data providers to give you data sets with incomplete data. For example, in a data set of GDP per country, you can find missing GDP figures in the earlier and later years. One reason is that there is no official data for those years from the country's government. It will be incorrect to conclude that their GDP is zero (DUH!) and show a zero value in the extracted data or a graph. The correct value is NULL, meaning you don't have the data yet. The end user correctly interprets the missing datapoints in the extracted data and graphs as NOT zero. Furthermore, it won't cause errors in your computations especially when you do averages.

Some "rules" that make sense theoretically would in fact be a poor or incorrect solution in your case.