106

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?
0

11 Answers 11

82

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!

5
  • 3
    +1 very few dba's ever need to worry about the speed/size differences of using NULL or not
    – Patrick
    Jan 4, 2011 at 0:52
  • 33
    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, 2011 at 1:27
  • 7
    Outstanding - NULL is not known, Empty String was specified.
    – ScottCher
    Jan 4, 2011 at 21:32
  • @Larry what's the performance impact? How does the performance vary with tables of many cols vs. tables of many rows? Mar 13, 2015 at 5:01
  • 1
    I agree that if there is a distinction between no value given and an empty string in your dataset then you should use them appropriately, but personally if I don't need that distinction with my data then I always use an empty string, purely because I find that query result from a MySQL client on the command line can be cleaner to look at with empty strings instead of lots of NULLs
    – RTF
    Nov 22, 2018 at 10:27
28

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 NULL || '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.

1
17

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 '').

1
  • 16
    If the user chose not to provide a gender, surely you should store "Declined to provide". NULL is ambiguous; it could also mean "the customer has not been asked", "the customer identifies with a gender not on our list", etc. Jan 7, 2015 at 15:52
12

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.

2
  • 4
    It is actually not the case that NULL != NULL, because that's NULL. ;-) Jan 4, 2011 at 5:10
  • 2
    Note that MS SQL does not follow this rule: multiple NULL values will violate a UNIQUE constraint. Fortunately, starting with 2008 you can use a filtered index to get proper behavior. Jan 7, 2015 at 15:50
7

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.

4

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.

3
  • the question is about NULL vs. empty string (in a nullable column, IMO), not NULL vs NOT NULL, isn't it?
    – Gan
    Jan 4, 2011 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, 2011 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, 2011 at 11:17
3

They are also different from a design perspective:

e.g.

CREATE TABLE t (
    id INTEGER  NOT NULL,
    name CHARACTER(40),
    CONSTRAINT t_PK PRIMARY KEY (id)
);

CREATE UNIQUE INDEX t_AK1 ON t (name);

Looks like:

 \d t
          Table "public.t"
 Column |     Type      | Modifiers
--------+---------------+-----------
 id     | integer       | not null
 name   | character(40) |
Indexes:
    "t_pk" PRIMARY KEY, btree (id)
    "t_ak1" UNIQUE, btree (name)

Lets insert some data:

op=# insert into t(id, name ) values ( 1, 'Hello');
INSERT 0 1

op=# insert into t( id, name) values ( 2, '');
INSERT 0 1

op=# insert into t( id, name) values ( 3, '');

ERROR:  duplicate key value violates unique constraint "t_ak1"

Now lets try with null:

op=# insert into t( id, name) values (4, null );

INSERT 0 1

op=# insert into t( id, name) values (5, null);

INSERT 0 1

This is allowed.

Soooooo: nulls are not trivial strings nor the reverse.

Cheers

1
  • That's PostGres. As other posters noted, in Oracle there is no difference. Jul 21, 2020 at 12:54
3

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 any 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 '' or a NOT NULL column that allows zero-len. (Both are not possible in Oracle, as '' === NULL there.)

  • 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.

To get back to my Oracle experience: Say you want to generate a query for a search request. If you (in non Oracle) 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 searchvalue is NULL).

The second, more complicated logic, is indeed what you have to do in Oracle, as NULL and '' are the same thing there.

1

If we talk about theory, then the Codd's rules say that RDBMS must treat NULL values in a special way.

How exactly that is used is up to database architects, depending on actual domain - task - project - application - area.

0

Depends on meanings. Take this example: your column stores what a person said the 12th of August 2020 between 8am and 9am UTC. Chances are that

  1. a person said something (non empty string)
  2. a person said nothing (empty string)
  3. you don't know what a person said (null value)

You can make your data mean what is more convenient to you by exploting (or not) what is offered to you

0

Generally speaking, a database NULL should be thought of as "This Is Unknown". In the case of something like a Date of Birth, or a Height In Centimeters column, it makes perfect sense to store a NULL there if the value is not known.

Getting to the meat of your question: strings. Say you have a Former Name column. This would be used to track a person's pre-marriage surname, for married people who take their partner's surname. Storing a NULL there should still mean "This Is Unknown". What about someone who is not married, or is married but didn't change their surname? Well, that is a KNOWN value, so it's NOT appropriate to use NULL there. That could be a good use of an empty string, but a more correct choice might be to just store their surname in that column as well.

In most relational databases, NULL has many special properties that make it unsuitable for general use. Generally speaking, any expression will return NULL if any of its operands is NULL. 1 + NULL = NULL. 'Some String' + NULL = NULL. NULL = NULL = NULL. One key exception is the IS NULL operator, which returns TRUE if its operand is NULL, FALSE otherwise. This "NULL poisoning" can easily cascade when you write complex expressions using many columns where the values can be NULL. This means that when writing expressions, you MUST be keenly aware of where NULL will pop up and take that into account.

It's best for everyone involved to always treat NULL as "unknown" rather than "empty", particularly for complex data types. When designing a relation, don't make a column nullable without good reason, and when working with a relation, always check each column for nullability and take that into account.

Someone else's response spells out the insanity of Oracle Database, which treats NULL as equivalent to the empty string. Avoid Oracle if at all possible, because that's just one of many insane things Oracle does and will never change.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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