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?

10 Answers 10


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!

  • 2
    +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
  • 30
    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
  • 6
    Outstanding - NULL is not known, Empty String was specified. – ScottCher Jan 4 '11 at 21:32
  • @Larry what's the performance impact? How does the performance vary with tables of many cols vs. tables of many rows? – Shimmy Weitzhandler Mar 13 '15 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 '18 at 10:27

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;


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.


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

  • 15
    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. – Jon of All Trades Jan 7 '15 at 15:52

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.

  • 4
    It is actually not the case that NULL != NULL, because that's NULL. ;-) – Peter Eisentraut Jan 4 '11 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. – Jon of All Trades Jan 7 '15 at 15:50

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.


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.

  • 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

They are also different from a design perspective:


    name CHARACTER(40),


Looks like:

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

Lets insert some data:

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

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

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 );


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


This is allowed.

Soooooo: nulls are not trivial strings nor the reverse.


  • That's PostGres. As other posters noted, in Oracle there is no difference. – Tom Warfield Jul 21 '20 at 12:54

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.


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.


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

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.