Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm interested mainly in MySQL and PostgreSQL, but you could answer the following in general:

  • Is there a logical scenario in which it would be useful to distinguish an empty string from NULL?
  • What would be the physical storage implications for storing an empty string as...

    • NULL?
    • Empty String?
    • Another field?
    • Any other way?
share|improve this question
    
I believe there is a minor storage increase to allow NULL, but there is an SELECT query efficiency that is created when checking for NULL vs. an empty string –  Patrick Jan 3 '11 at 23:49
1  
Which DBMS are we talking about here? And what language to access? –  Brian Ballsun-Stanton Jan 4 '11 at 0:19
1  
@Brian Ballsun-Stanton: Edited. I'm not sure that language used to access is relevant here. I know that it could have implications on language and application but I'm interested on DB. –  bigown Jan 4 '11 at 0:23
1  
@Patrick: Using null values can also save space. –  Peter Eisentraut Jan 4 '11 at 5:09

7 Answers 7

up vote 38 down vote accepted

Let's say that the record comes from a form to gather name and address information. Line 2 of the address will typically be blank if the user doesn't live in apartment. An empty string in this case is perfectly valid. I tend to prefer to use NULL to mean that the value is unknown or not given.

I don't believe the physical storage difference is worth worrying about in practice. As database administrators, we have much bigger fish to fry!

share|improve this answer
1  
+1 very few dba's ever need to worry about the speed/size differences of using NULL or not –  Patrick Jan 4 '11 at 0:52
13  
Agreed ... I try to reserve NULL for 'not known' ... empty string is 'we know it should be empty'. It's particularly useful for when your data comes from multiple sources –  Joe Jan 4 '11 at 1:27
2  
Outstanding - NULL is not known, Empty String was specified. –  ScottCher Jan 4 '11 at 21:32

I do not know about MySQL and PostgreSQL, but let me treat this a bit generally.

There is one DBMS namely Oracle which doesn't allow to choose it's users between NULL and ''. This clearly demonstrates that it is not necessary to distinguish between both. There are some annoying consequences:

You set a varchar2 to an empty string like this:

Update mytable set varchar_col = '';

the following leads to the same result

Update mytable set varchar_col = NULL;

But to select the columns where the value is empty or NULL, you have to use

select * from mytable where varchar_col is NULL;

Using

select * from mytable where varchar_col = '';

is syntactically correct, but it never returns a row.

On the other side, when concatenating strings in Oracle. NULL varchars are treated as empty strings.

select '' || 'abc' from DUAL;

yields abc. Other DBMS would return NULL in these cases.

When you want to express explicitly, that a value is assigned, you have to use something like ' '.

And you have to worry whether trimming not empty results in NULL

select case when ltrim(' ') is null then 'null' else 'not null' end from dual

It does.

Now looking at DBMS where '' is not identical to NULL (e.g. SQL-Server)

Working with '' is generally easier and in most case there is no practical need to distinguish between both. One of the exceptions I know, is when your column represents some setting and you have not empty defaults for them. When you can distinguish between '' and NULL you are able to express that your setting is empty and avoid that the default applies.

share|improve this answer
    
related : stackoverflow.com/questions/203493/… –  Joe Jan 4 '11 at 16:53
2  
+1 for pointing out this Oracle oddity...this was a HUGE "WTF" moment for me when I first started dealing with Oracle databases. –  TML Jan 5 '11 at 7:16
1  
+1 for "not necessary". –  Leigh Riffel Jan 19 '11 at 18:08
    
+1 for Working with '' is generally easier –  Martin Oct 3 '11 at 12:45

It depends on the domain you are working on. NULL means absence of value (i.e. there is no value), while empty string means there is a string value of zero length.

For example, say you have a table to store a person' data and it contains a Gender column. You can save the values as 'Male' or 'Female'. If the user is able to choose not to provide the gender data, you should save that as NULL (i.e. user did not provide the value) and not empty string (since there is no gender with value '').

share|improve this answer

One thing worth keeping in mind is that when you have a field that is not required, but any values that are present must be unique will require you to store empty values as NULL. Otherwise, you'll only be able to have one tuple with an empty value in that field.

There are also some differences with relational algebra and NULL values: NULL != NULL, for instance.

share|improve this answer
3  
It is actually not the case that NULL != NULL, because that's NULL. ;-) –  Peter Eisentraut Jan 4 '11 at 5:10

A new thought, a big influence on your choice of NULL / NOT NULL is if you are using a framework. I use symfony alot and using allowing NULL fields simplifies some of the code and data checking when manipulating the data.

If you are not using a framework or if you are using simple sql statements and processing, I would go with whichever choice you feel is simpler to keep track of. I generally prefer NULL so that doing INSERT statements don't get tedious with forgetting to set the empty fields to NULL.

share|improve this answer
    
the question is about NULL vs. empty string (in a nullable column, IMO), not NULL vs NOT NULL, isn't it? –  Gan Jan 4 '11 at 2:01
    
the part of the question asking about storage led me to think that he may be thinking about Null/Not Null as well –  Patrick Jan 4 '11 at 2:12
    
or @everyone else concerning the implication of NULL vs NOT NULL, you may refer to this: dba.stackexchange.com/q/63/107 –  Gan Jan 4 '11 at 11:17

You might also factor in Date's critique of NULL and the problems of 3VL in SQL and Relational Theory (and Rubinson's critique of Date's critique, Nulls, Three-Valued Logic, and Ambiguity in SQL: Critiquing Date’s Critique).

Both are referenced and discussed at length in a related SO thread, Options for eliminating NULLable columns from a DB model.

share|improve this answer

Having had to work with Oracle (which doesn't allow you to differentiate) I have come to the following conclusion:

  • From a logical POV it doesn't matter. I really can't think of a compelling example where differentiating between NULL and zero-length-string adds any value in the DBMS.

  • From which follows: You either have a NULLable column that doesn't allow zero-len '' (Oracle-ish solution) or a NOT NULL column that allows zero-len.

  • And from my experience, '' makes a lot more sense when processing the data, as normally you would like to process the absence of a string as the empty string: Concatenation, Comparison, etc.

Note: To get back to my Oracle experience: Say you want to generate a query for a search request. If you use '' you can just generate WHERE columnX = <searchvalue> and it will work for equality searches. If you use NULL you have to do WHERE columnX=<searchvalue> or (columnX is NULL and serchvalue is NULL). Bah! :-)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.