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

In the context of the relational database model, null indicates "no value" or "unknown value". It exists for exactly the purpose you describe.

UPDATE: Sorry, I forgot to add that while most (all?) RDMBSs use this same definition for null, there are nuanced differences in how null is handled. For example, MySQL and Oracle allow multiple nulls in a UNIQUE column (or set of columns), because null is not a value, and cannot be considered unique (null != null). But the last time I used MS SQL Server, it only allowed a single null. So you might need to consider the RDBMS behavior, and whether the column in question will be constrained or indexed.

Answer 2

As far as I can tell, Oracle doesn't distinguish a difference.

select 1 from (select '' as col  from dual) where col is null;

Answer 3

Neither. Represent absence of data as absence of tuples in a relation.

For performance reasons you might want to avoid joins in some RDBMS' but try to design the model so that the information that can be missing is in a seperate relation.

Answer 4

Create a separate table for just the nullable column and a foreign key to the main table. If a record doesn't have data for that column then it won't have a record in the second table. This is the cleanest solution and you don't have to worry about handling nulls or giving special meaning to empty strings.

Answer 5

NULL is a non-value that should be relegated to the dark ages from where it sprung. I have found that there is a non-trivial amount of programming required to handle special NULL cases that could easily be handled with a default value.

Set the default for your column to be an empty string. Force the column to not allow null, which would most likely never happen once you assign a default value. Write your code blissfully ignoring the case where the column value is null.

One huge issue I have always had with NULL is that "SELECT * from tbl WHERE column = NULL" will always return an empty result set. NULL can never be equal to anything, including NULL. The speical keyword "column is null" is the only way to check for something being null. If you back away from null, then the comparison will succeed: "column = ''" 7 rows returned.

I've done two major DB implementations from scratch where in the end I've regretted using NULL. Next time, no NULLs for me!

Answer 6

I find NULL values to be helpful for referential integrity. In the case of MySQL if a field is set to NOT NULL, then an insert requires the data to be set; otherwise, NULL is a possible value and Foreign Key constraint is not enforced.

1. id: primary key
2. product_id: FOREIGN KEY NOT NULL
3. ref_id: (NULLABLE)

id and product_id area always required. ref_id can be set to NULL. However, if any other value is used it must satisfy the FOREIGN KEY constraint.