SQL: empty string vs NULL value

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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: https://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.

Answer 5

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.

Answer 6

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.

Answer 7

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?

Answer 8

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

• You have valid email addresses: [email protected]
• 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.

Answer 9

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.

Answer 10

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?

Answer 11

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.

Answer 12

I don't think it matters much, but I like it better when the NULL is there.

When I view the data displayed in a table (like in SQL Server Management Studio), I can better distinguish a missing value if it says NULL and the background is of different colour.

If I see a blank space, I am always wondering if it's really empty or there's some whitespace or some invisible characters. With NULL it's guaranteed empty on the first sight.

I usually don't distinguish the values in the application, because it's unexpected and weird that NULL and empty string would mean something different. And most of the time, I take a defensive approach and just deal with both states. But for me as a human, NULL is easier to process when looking at the data.