When to use NULL and when to use an empty string?

Question

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?

Answer 1

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!

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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

Answer 8

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.

Answer 9

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.

Answer 10

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.

Answer 11

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