Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I know this subject is a bit controversial and there are a lot of various articles/opinions floating around the internet. Unfortunatelly, most of them assume the person doesn't know what the difference between NULL and empty string is. So they tell stories about surprising results with joins/aggregates and generally do a bit more advanced SQL lessons. By doing this, they absolutely miss the whole point and are therefore useless for me. So hopefully this question and all answers will move subject a bit forward.

Let's suppose I have a table with personal information (name, birth, etc) where one of the columns is an email address with varchar type. We assume that for some reason some people might not want to provide an email address. When inserting such data (without email) into the table, there are two available choices: set cell to NULL or set it to empty string (''). Let's assume that I'm aware of all the technical implications of choosing one solution over another and I can create correct SQL queries for either scenario. The problem is even when both values differ on the technical level, they are exactly the same on logical level. After looking at NULL and '' I came to a single conclusion: I don't know email address of the guy. Also no matter how hard i tried, I was not able to sent an e-mail using either NULL or empty string, so apparently most SMTP servers out there agree with my logic. So i tend to use NULL where i don't know the value and consider empty string a bad thing.

After some intense discussions with colleagues i came with two questions:

  1. am I right in assuming that using empty string for an unknown value is causing a database to "lie" about the facts? To be more precise: using SQL's idea of what is value and what is not, I might come to conclusion: we have e-mail address, just by finding out it is not null. But then later on, when trying to send e-mail I'll come to contradictory conclusion: no, we don't have e-mail address, that @!#$ Database must have been lying!

  2. Is there any logical scenario in which an empty string '' could be such a good carrier of important information (besides value and no value), which would be troublesome/inefficient to store by any other way (like additional column). I've seen many posts claiming that sometimes it's good to use empty string along with real values and NULLs, but so far haven't seen a scenario that would be logical (in terms of SQL/DB design).

P.S. Some people will be tempted to answer, that it is just a matter of personal taste. I don't agree. To me it is a design decision with important consequences. So i'd like to see answers where opion about this is backed by some logical and/or technical reasons.

share|improve this question
7  
Are you aware that in Oracle, the empty string is NULL? –  user281377 Dec 30 '10 at 12:29
4  
@ammoQ: Oracle's treatment of zero-length strings is non-standard. Besides, '' even in Oracle, is not the same as NULL. For example, assigning a CHAR(1) column the value '' will result in ' ' (i.e. a space), not NULL. Besides, if Jacek was using Oracle, this question would likely not even come up :-) –  Dean Harding Dec 30 '10 at 13:07
1  
Dean: You are right about the char(1) example, but that's yet another WTF, since '' IS NULL evaluates to true in PL/SQL. –  user281377 Dec 30 '10 at 13:12

11 Answers 11

up vote 50 down vote accepted

I would say that NULL is the correct choice for "no email address". There are many "invalid" email addresses, and "" (empty string) is just one. For example "foo" is not a valid email address, "a@b@c" is not valid and so on. So just because "" is not a valid email address is no reason to use it as the "no email address" value.

I think you're right in saying that "" is not the correct way to say "I don't have a value for this column". "" is a value.

An example of where "" might be a valid value, separate to NULL could be a person's middle name. Not every one has a middle name, so you need to differentiate between "no middle name" ("" - empty string) and "I don't know if this person has a middle name or not" (NULL). There's probably many other examples where an empty string is still a valid value for a column.

share|improve this answer
3  
Totally agree. NULL is there for a reason. SELECT COUNT(*) FROM YOURTABLE WHERE EMAIL IS [NOT] NULL is the way to do it, not string comparison which will tend to be slower (even for empty strings I suppose but I'm not sure of this one :). –  LudoMC Dec 30 '10 at 14:03
2  
I think NULL does not mean that there is no email address, i think it means that the email address is currently not known, not known to exist, or is impossible to fill in for other reasons. Fortunately, the is probably no situation where one would want to keep in a database the information about people who truly do not have and do not plan to have any email address, otherwise a separate boolean field would probably be necessary. –  Alexey Aug 8 '12 at 9:28
4  
@Alexey - NULL means there is no value. As others have pointed out an empty string is a value. –  Ramhound Aug 9 '12 at 11:43
1  
@Ramhound, i agree that the empty string is a value, and that NULL vaguely means "there is no value". I just explained my interpretation of "no value". In my opinion, it is not the same as "the person has not opened any email account". It is rather "no email address recorded for that person". –  Alexey Aug 9 '12 at 19:08
3  
@Ramhound NULL means there is no value. A person without a middle name has no value there. Therefore, NULL should be used in a middle initial column as well... Which is completely opposite the argument presented in this answer. –  Izkata Aug 13 '12 at 22:00

While agreeing with the above comments, I would add this argument as a primary motivation:

  1. It is obvious to any programmer looking at a database that a field marked NULL is an Optional field. (i.e. the record doesn't require data for that column)
  2. If you mark a field NOT NULL, any programmer should intuitively assume that it is a Required field.
  3. In a field that allows nulls, programmers should expect to see nulls rather than empty strings.

For the sake of Self-Documenting Intuitive Coding, use NULL instead of empty strings.

share|improve this answer
1  
+1 This is the "least astonishment" argument with respect to developers against empty strings. No developer that comes later would ever expect that empty strings would be used to represent "no email address". –  Thomas Aug 8 '12 at 15:51

for the specific technical question, the issue is not null vs empty-string, it is a validation failure. An empty string is not a valid email address!

for the philosophical question, the answer is similar: validate your inputs. If an empty string is a valid value for the field in question, then expect it and code for it; if not, use null.

An empty string would be a valid input to answer the question: What did the mime say to the giraffe?

share|improve this answer
    
Even with the best intent in the world, validation may not solve this problem -- he may still have to use a method dealing with rows where all columns must be supplied with some kind of value. In that case, the question will remain -- what value to use when there is no value? And the answer will of course be: the value that indicates no value. In DBs this commonly NULL. –  jmoreno Mar 15 '13 at 5:24

I think Dean Hardings answer covers this really nicely. That said I would like to mention that when talking about NULLs vs empty strings at the DB level you should have a think about your other data types. Would you store min date when no date is supplied? or -1 when no int is supplied? Storing a value when you have no value means you then have to keep track of a whole range of non values. At least one for each data type (possibly more as you get cases where -1 is an actual value so you need to have some alternative etc). If you need/want to do something "fudgy" at the application level that is one thing but their is no need to pollute your data.

share|improve this answer
1  
+1 - This is what I call the "Magic Value Solution". We have to come up with a magic value for each data type to represent an absence of a value. In addition, in some columns the common magic value is or becomes a legitimate value and thus a new magic value is needed. –  Thomas Aug 8 '12 at 16:06

Unfortunately, Oracle confused the representation of VARCHAR string of length zero with the representation of NULL. They are both represented internally by a single byte with value zero. This makes the discussion just that much harder.

A lot of the confusion surrounding NULL centers around three-valued logic. Consider the following pseudocode:

if ZIPCODE = NULL
    print "ZIPCODE is NULL"
else if ZIPCODE <> NULL
    print "ZIPCODE is not NULL"
else print "Something unknown has happened"

You wouldn't expect the third message, but that's what you would get, under three valued logic. Three valued logic leads people towards numerous bugs.

Another source of confusion is drawing inferences from the absence of data, like drawing an inference from the dog that didn't bark in the night. Often these inferences were not what the writer of the NULL intended to cnvey.

Having said that, there are plenty of situations where NULL handles the absence of data just fine, and produces exactly the results you want. One example is foreign keys in optional relationships. If you use a NULL to indicate no relationship in a given row, that row will drop out of an inner join, just as you would expect.

Also, be aware that even if you avoid NULLS completely in the stored data (sixth normal form), if you do any outer joins, you are still going to have to cope with NULLS.

share|improve this answer

In your example if it is value directly from web field - I would use empty string. If user could option to specify that he don't want to provide email, or could delete it - then NULL.

Here are link with points that you could consider: http://stackoverflow.com/questions/405909/null-vs-empty-when-dealing-with-user-input/405945#405945

--- edited (In reply to Thomas comment) ---

Databases don't live without applications that use them. Defining NULL or '' have no value, if application can't use it properly.

Consider one example where user is filling LONG form and hit enter, that will send persist request to server. He could be in the middle of entering his email. Most probably you want to store whatever he have in email field, so later he could finish it. What if he entered only one character? What if he entered one character and then delete it? When email is not required, sometimes users want to delete it: easiest way to just clear field. Also in case when email is not required it is worth to validate it before sending.

Another example: user provide email as spamto@[bigcompany].com - in that case there is no need to send email, even so it is exist and valid (and may be even exist). Sending one such maybe cheap, but if there are 10K users with such emails for daily subscriptions, then such validation may saved a lot of time.

share|improve this answer
3  
-1. Whether the database is driving a website or not is irrelevant. Designing databases is different world than web design. The database should be designed to capture facts about the business domain independent of interface used to write to it. By your logic, should you use nulls if coincidentally the first application is an executable? What happens if the first app is a web application but the next application is a mobile app? Design the database to capture facts using normalization rules and design the web site to write to it. –  Thomas Aug 8 '12 at 16:11
    
I'm glad that you learned how to write and comment on this site :) I still believe that DB should support application that uses it. Check my edited answer. –  Konstantin Petrukhnov Aug 9 '12 at 7:34
2  
Databases don't live without applications that use them. In my experience, this is simply not true and short-sighted. Almost always the database gets used outside of the application for which it was designed. In general, databases survive longer than the applications for which they were built. Databases should be designed to collect facts about the business and the UI should be built to read and write to the database not the other way around. Relational design is an entirely different mindset than application design. –  Thomas Aug 9 '12 at 15:39
    
Could you give example of database that not used by any application? :) –  Konstantin Petrukhnov Aug 10 '12 at 8:12
2  
Examples where the database is not used solely by the original application: reports, integrations with other systems. –  Thomas Aug 10 '12 at 15:08

Use Null.

There's no point to storing a value of '', when simply making the field in the table nullable will do. It makes queries more obvious too.

Which SQL query is more obvious and readable if you wanted to find users with an email address?

  1. SELECT * FROM Users WHERE email_address != ''

  2. SELECT * FROM Users WHERE email_address IS NOT NULL

  3. SELECT * FROM Users WHERE email_address != '' and email_address IS NOT NULL

I would say 2 is. Although 3 is more robust in the cases where there's bad data stored.

For the case of the email address on the form, which is optional, it should be reflected in the table too. In SQL, it's a nullable field, which means it's not known.

I can't think of any reasonable business value in storing an empty string in a table other than simply bad design. It's like storing a string value of 'NULL' or 'BLANK', and having developers assume that it's null or a empty string. To me, that's bad design. Why store that when there's NULL??

Just use NULL, and you'll make everyone a little bit more happy.

MORE INFO:

SQL uses a three valued logic system: True, False, and Unknown.

For a better and more detail explanation, I recommend developers to read: SQL Queries – beyond TRUE and FALSE.

share|improve this answer

The question as I understand it, is which interpretations of NULL and empty string should be chosen. This depends on how many states the particualar field can be in.

The interpretation depends on how the database is being accessed. If there is a layer in the code that abstracts out the database completely, than choosing any policy (including two-coulmn) that works is completely acceptable. (Clearly documenting the policy is important, though). However, if the database is being accessed in several places, then you should use a very simple scheme, since code will be harder to maintain and may be erroneous in this case.

share|improve this answer

Well basically on logical level there's no difference between "invalid" value and "no user input", they're just all "special cases" most of the time. Error case.

Having null takes additonal space: ceil(columns_with_null/8) in bytes / per row.

Empty cell and null are both way to mark something is wrong / should be default. Why would you need 2 "wrong" states? Why use NULLs if they take additional space and mean exactly the same as empty strings? That will just introduce confusion and redundancy when you're having two things meaning (that could mean) exactly the same, it's easy to forget that you should use NULLs instead of empty strings (if eg. user ommited some fields).

And your data can become a mess. In a perfect world you'd say "the data will be always correct and i'll remember"... but when people have to work in a team and not everybody is exactly on your level it's not uncommon to see WHERE (aa.xx <> '' AND bb.zz IS NOT NULL)

So instead of correcting my team members every other day i just enforce simple rule. No null values, NEVER!

Counting NON-NULL values is faster... simple question is what would you need to do that for?

share|improve this answer
    
I vaguely recall reading somewhere that using NULL is actually a cost (both in terms of computation and storage) for the database. So good point in bringing that formula up. –  Jacek Prucia Jan 4 '11 at 8:44
    
Don't forget that a VARCHAR column will take at least 1 byte to store the length of the string, even if it's zero. –  dan04 Feb 21 '11 at 0:01
    
Empty cell and null are both way to mark something is wrong. Not true. A null is a way to indicate an absence of a value. I bet most RDBMS use a bit array on each row to indicate which columns are null. Thus, the additional space is so tiny as to be irrelevant. Worrying about the additional processing is premature optimization and will be nothing compared to the speed bumps created for other developers to "discover" that you have intentionally used empty strings. –  Thomas Aug 8 '12 at 16:00
1  
No null values. This is the Ostrich approach. "We'll stick our head in the sand and declare that absent values do not exist". That usually leads to the Magic Value Solution where you have to come up with a magic value for each data type to represent an absence of a value. –  Thomas Aug 8 '12 at 16:04

I tend to view it not from the DB perspective but from an program perspective. I know that this question is for the SQL click but really, how many users access data directly any longer?

In a program I don't like null/nothing. There are a few exceptions but they are just that. And those exceptions are really just bad implementations.

So if the user didn't put in the email it there should be something that determines if this is valid or not. If a blank email is fine then it displays a blank string. If the user did not put in an email and that violates a rule the object should indicate this.

The idea of null having meaning is old school and is something modern programmers have to work around.

Even in DB design why can't the email field not allow nulls and have a zero length string and have another field indicating if the user input something? Is one bit that much to ask of a DBMS? The DB shouldn't, in my opinion, handle neither the business logic nor the display logic. It wasn't built for that and thus does a very poor job of handling it.

share|improve this answer
    
why can't the email field not allow nulls and have a zero length string - Simply put: because any developer that knows anything about databases would never expect that empty strings having magic meaning. You are attempting to make your own magic value to represent what fundamentally already exists in every database: a concept to represent an absence of a value. Why reinvent the wheel? Also, the idea of NULLS is far, far, far from old school. Nulls are keystone to understanding relational database design. –  Thomas Aug 8 '12 at 15:55
    
LOL. Like I said from a programmers perspective nulls are almost always a pain in the butt and almost never needed for BUSINESS LOGIC. I personally, as a developer, don't care much for relational design. If I did I would be a DB dude. If I get a null from a DB I almost always convert it to something rational, like an empty string then let my glorious OOP design do it's magic. The framework takes care of those silly nulls DBAs force upon the world. I know DB dudes have to deal with it and I feel for you. But as a programmer I don't have to. I have better solutions. –  ElGringoGrande Aug 10 '12 at 2:31
    
You "never" have to deal with nulls. So, what you describe is ostrich solution combined with the the magic value solution. "I'll ignore the fact that absent values exist and I'll convert all null integers to -1". Until the day comes when -1 is a real value. It should be noted that one of the reasons MS added generics to .NET was to address the massive impedance mismatch between databases and applications code and that primarily revolved around expressing nulls in middle-tier code. Those "silly nulls" exist in business logic too. –  Thomas Aug 10 '12 at 15:14
    
The fact that some integer is absent in the db (or is null) doesn't mean I have to represent it with -1 or evan a nullable(int). If you think that is the only way to deal with nulls then you don't understand programming very well. Remember null is not the same thing as nothing. Like you said, null represents a place holder for absent values in some kind of data struct. It means something. Business logic rarely (which is not the same as never) needs this concept because it is about beahvior, not data. And when it does null is rarely the best way to represent this. –  ElGringoGrande Aug 11 '12 at 18:34
    
Even business logic has to account for (meaning represent) absent values and that is true in my experience, in almost every system I've seen or built in the past 20 years. The database is modeling the business facts to be captured and stored. If the business logic wants to be able to interact with the database it must know how to deal with nulls. Whether it is a custom struct, magic value or a generic is irrelevant. The business logic needs the ability to handle the receipt of an absent value from the database and the ability to mark a value as absent to the database. –  Thomas Aug 12 '12 at 15:37

I could think of a reason for having NULL and the empty string:

  • You have valid email addresses: me@example.com
  • You have none (and probably should ask for one): NULL
  • You know that this person does not have an email address: Empty String.

However I would not recommend that and use a separate field for whether to ask whether you know that none is existing.

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.