Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though.

All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).

But what do you do in the case of dates, datetime, and time (SQL Server 2008)? You'd have to use some historic or bottomed-out date.

Any ideas on this?

share|improve this question
This answer has an insight on usage of NULL… – Derek Downey Aug 30 '11 at 21:24
Really? Why does RDBMS allow us to use NULL at all, if we shouldn't use them? There's nothing wrong with NULL as long as you know how to deal with them. – Fr0zenFyr Aug 13 '13 at 6:19
Was this a BI data modeling? You generally should not allow nulls in fact tables... otherwise, nulls are you friends when used properly. =) – sam yi Sep 3 '13 at 18:08
@Fr0zenFyr, just because an RDBMS allows us to do something it is not necessarily a good idea to do so. Nothing forces us to declare a primary key or a unique key in a table, but with few exceptions we do anyhow. – Lennart Apr 4 at 18:53
up vote 152 down vote accepted

I think the question is poorly phrased, as the wording implies that you've already decided NULLs are bad. Perhaps you meant "Should we allow NULLs?"

Anyway, here is my take on it: I think NULLs are a good thing. When you start preventing NULLs just because "NULLs are bad" or "NULLs are hard", you start making up data. For example, what if you don't know my birth date? What are you going to put in the column until you know? If you're anything like a lot of anti-NULL folks, you're going to enter 1900-01-01. Now I'm going to be placed in the geriatric ward and probably get a call from my local news station congratulating me on my long life, asking me my secrets to living such a long life, etc.

If a row can be entered where it is possible that you don't know the value of a column, I think NULL makes a lot more sense than picking some arbitrary token value to represent the fact that it is unknown - a value which others will have to already know, reverse engineer, or ask around to figure out what it means.

There is a balance, though - not every column in your data model should be nullable. There are often optional fields on a form, or pieces of information that otherwise don't get collected at the time the row is created. But that doesn't mean you can defer populating all of the data. :-)

Also the ability to use NULL can be limited by crucial requirements in real life. In the medical field, for example, it can be a life-or-death matter to know why a value is unknown. Is the heart rate NULL because there wasn't a pulse, or because we haven't measured it yet?

Don't be afraid of NULLs, but be willing to learn or dictate when and where they should be used, and when and where they shouldn't.

share|improve this answer
I think I agree with you. What I think is even more important, though, is that the standard is set and followed. If there is no string input, across the board it has to be NULL, instead of an empty string. I can't stand expecting one or the other for the lack of data. +1, @AaronBertrand. – Thomas Stringer Aug 31 '11 at 1:28
@bignose so you have an entity like Customer, and 4 or 5 attributes that you may or may not know when the row is created, you should have 4 or 5 tables for the extra attributes? And create a new table each time you add a new attribute? I think you can maintain that in a classroom but not in a production system. What is wrong with having NULL instead of an absent row? Logically they are similar but now your joins become quite tedious to write... – Aaron Bertrand Sep 25 '11 at 20:39
@bignose you're avoiding my question. I think your answer paints NULLs as this wicked witch of the west, and most of your reasoning involves lack of education about NULL, but you ignore what to do when you have an entity with several possibly unknown attributes. So to avoid NULLs entirely, you'd advocate a design that had 15 tables that may or may not have rows, compared to a single table with 15 nullable columns? You'd have a real hard time pulling that off in any of the shops I've worked/consulted. – Aaron Bertrand Sep 27 '11 at 11:34
I haven't advocated avoiding NULLs entirely with current SQL capabilities. Rather, I point out the failings of NULLs and their undesirability, and advocate changing the RDBMS state of art to a point where we can feasibly avoid NULL. – bignose Sep 27 '11 at 21:47
@bignose your suggestion above was advocating putting those "truths" in separate tables. All that does is shift the "dealing with NULL" problem from a COALESCE to an OUTER JOIN - and lots more of them if we're talking about more than one attribute. I don't see anything in there about changing the way the RDBMS works. Perhaps you could elaborate and explain how that would work in this context. – Aaron Bertrand Sep 27 '11 at 22:36

Established reasons are:

  • NULL is not a value, and therefore has no intrinsic data type. Nulls need special handling all over the place when code that otherwise relies on actual types might also receive the un-typed NULL.

  • NULL breaks two-value (familiar True or False) logic, and requires a three-value logic. This is far more complex to even implement correctly, and is certainly poorly understood by most DBAs and just about all non-DBAs. As a consequence, it positively invites many subtle bugs in the application.

  • The semantic meaning of any specific NULL is left to the application, unlike actual values.

    Semantics like “not applicable” and “unknown” and “sentinel” are common, and there are others too. They are frequently used simultaneously within the same database, even within the same relation; and are of course implicit and indistinguishable and incompatible meanings.

  • They aren't necessary to relational databases, as argued in “How To Handle Missing Information Without Nulls”. Further normalisation is an obvious first step to try ridding a table of NULLs.

This doesn't mean NULL should never be allowed. But likewise, it does argue that there are many good reasons to disallow NULL wherever feasible, and try very hard to make it feasible to avoid NULL.

Fabian Pascal has an exchange with someone asking essentially the same question: “NULL confusion”.

share|improve this answer
Your link to "How To Handle Missing Information Without Nulls" shows quite nicely why we can't do without nulls: Several of the suggestions would be impossible to implement in a rational way on the major RDBMSs as they currently stand. – Jack Douglas Sep 24 '11 at 12:28
Jack: Right, but “the current implementations can't do it” is not an argument for the status quo :-) – bignose Sep 24 '11 at 12:32
Is that kind of like saying we shouldn't fly because planes aren't perfect? – Aaron Bertrand Sep 27 '11 at 11:48
No, it's saying that the vendors should stop invoking excuses for nulls that might have been valid ones forty years ago, but have long outlived their reasonable retention period. I/O times are no longer in the order of magnitude of 80ms. Single CPU cycles are no longer in the order of magnitude of microseconds. Memory limits are no longer in the order of magnitude of a few Megs. Unlike forty years ago, the hardware speeds and capacities needed for working without nulls now DO exist with the cost not being prohibitive. He's saying it's time to move on. – Erwin Smout Oct 5 '12 at 13:30
Actually I found one area where they are necessary in relational databases, namely in foreign keys, particularly if it is a composite key. You can probably factor these out to some extent but you cannot have a two column foreign key where one portion is NULL without handling that with some pretty interesting acrobatics DDL-wise. – Chris Travers Nov 7 '13 at 6:47

I disagree, nulls are an essential element of database design. The alternative, as you alluded too, would be a proliferation of known values to represent the missing or unknown. The problem lies with null being so widely misunderstood and as a result being used inappropriately.

IIRC, Codd suggested the current implementation of null (meaning not present/missing) could be improved by having two null markers rather than one, "not present but applicable" and "not present and not applicable". Can't envisage how relational designs would be improved by this personally.

share|improve this answer
I suggest having a user-defined set of different kinds of null, and a user-defined multi-valued logic to go with them :p – Jack Douglas Sep 24 '11 at 12:32
Those aren't the only options. You exclude the normalisation alternative: Instead of columns which may or may not have a value, use another table which may or may not have a corresponding row for the first table. The meaning of the presence or absence of a row is entailed in the meaning of the tables, and there's no special-casing of NULL or sentinel values etc. – bignose Sep 24 '11 at 22:35
The presence of NULL does not necessitate special-casing or sentinel values. Those are just symptoms of how some folks decide to deal with NULLs. – Aaron Bertrand Sep 25 '11 at 20:43
It's worth noting that '' is distinct from null on PostgreSQL (though not Oracle) and so gives you a two-fold marker, and you could use 0 for numeric columns. The problem with 0 though is that it doesn't work for foreign keys. – Chris Travers Nov 7 '13 at 6:43

Interesting questions.

All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).

It's more complicated than that. Null has a number of distinct meanings and one really important reason not to allow nulls in many columns is that when the column is null this then means one and only one thing (namely that it didn't show up in an outer join). Additionally it allows you to set minimum standards of data entry which is really helpful.

But what do you do in the case of dates, datetime, and time (SQL Server 2008)? You'd have to use some historic or bottomed-out date.

That illustrates a problem with nulls right away, namely that a value stored in a table can mean either "this value does not apply" or "we don't know." With strings, an empty string can serve as "this does not apply" but with dates and times, there is no such convention because there is no valid value which conventionally means this. Typically there you will be stuck using NULLs.

There are ways of getting around this (by adding more relations and joining) but those pose the exact same semantic clarity problems that having NULLs in the database do. For these databases I wouldn't worry about this. There just isn't anything you can do about it really.

EDIT: One area where NULLs are indispensable is in foreign keys. Here they typically have only one meaning, identical to the null in outer join meaning. This is an exception to the problem of course.

share|improve this answer

Let me start off by saying I am not a DBA, I am a developer by heart and I maintain and update our databases based on our needs. That being said, I had the same question for a few reasons.

  1. Null values make development more difficult and bug prone.
  2. Null values make queries, stored procedures, and views more complex and bug prone.
  3. Null values take up space (? bytes based on fixed column length or 2 bytes for variable column length).
  4. Null values can and often affect indexing and mathematics.

I spend a very long time sifting through the loads of responses, comments, articles, and advice all over the internet. Needless to say most of the information was about the same as @AaronBertrand's response. Which is why I felt the need to respond to this question.

Firstly I want to set something straight for all future readers... NULL values represent unknown data NOT unused data. If you have an employee table that has a termination date field. A null value in the termination date is because it is a future required field which is currently unknown. Every employee be it active or terminated will at some point have a date added to that field. That is in my opinion the one and only reason for a Nullable field.

That being said the same employee table would most likely hold some kind of authentication data. It is common in an enterprise environment that employees will be listed in the database for HR and accounting but don't always have or need authentication details. Most of the responses would lead you to believe it is okay to null those fields or in some cases create an account for them but never send them the credentials. The former will cause your development team to write code to check for NULLs and to deal with them accordingly and the latter poses a huge security risk! Accounts that are never used yet in the system only increase the number of possible access points for a hacker, plus they take up valuable database space for something that is never used.

Given the information above, the best way to deal with nullable data that WILL be use is to allow for nullable values. It is sad but true and your developers will hate you for it. The second type of nullable data should be put in a related table (IE: Account, Credentials, etc) and have a One-to-One relationship. This allows for a user to exist without credentials unless they are needed. This removes the extra security risk, valuable database space, and provides for a much cleaner database.

Below is a very simplistic table structure showing both the required nullable column and a One-to-One relationship.

Unknown Nullable and One-to-One relationship

I know I am a little late to the party since this question was asked years ago but hopefully this will help to shed some light on this issue and how best to deal with it.

share|improve this answer

Wikipedia's article on SQL Null has some interesting remarks about the NULL value, and as a database-agnostic answer, as long as you are aware of the potential affects of having NULL values for your specific RDBMS, they are acceptable in your design. If they were not, you wouldn't be able to specify columns as nullable.

Just be aware of how your RDBMS handles them in SELECT operations such as mathematics, and also in Indexes.

share|improve this answer

Apart from all the issues with NULL confusing developers, NULLs have another very serious drawback: Performance

NULL'able columns are a disaster from a performance perspective. Consider integers arithmetic as an example. In a sane world without NULL, it is "easy" to vectorise integer arithmetic in the database engine code using SIMD instructions to perform pretty much any calculation at speeds faster than 1 row per CPU cycle. However, the moment you introduce NULL, you need to handle all the special cases that NULL creates. Modern CPU instruction sets (read: x86/x64/ARM and GPU logic too) are simply not equipped to do this efficiently.

Consider division as an example. At a very high level, this is the logic you need with a non null integer:

if (b == 0)
  do something when dividing by error
  return a / b

With NULL, this becomes a little more tricky. Together with b you will need an indicator if b is null and similarly for a. The check now becomes:

if (b_null_bit == NULL)
   return NULL
else if (b == 0) 
   do something when dividing by error
else if (a_null_bit == NULL)
   return NULL
   return a / b

The NULL arithmetic is significantly slower to run on a modern CPU than the not null arithmetic (by a factor of around 2-3x).

It gets worse when you introduce SIMD. With SIMD, a modern Intel CPU can perform 4 x 32-bit integer divisions in a single instruction, like this:

x_vector = a_vector / b_vector
if (fetestexception(FE_DIVBYZERO))
   do something when dividing by zero
return x_vector;

Now, there are ways to handle NULL in SIMD land too, but this requires using more vectors and CPU registers and doing some clever bit masking. Even with good tricks, the performance penalty of the NULL integer arithmetic creeps into the 5-10x slower range for even relatively simple expressions.

Something like the above holds for aggregates and to some extent, for joins too.

In other words: The existence of NULL in SQL is an impedance mismatch between database theory and the actual design of modern computers. There is a pretty good reason NULL confuses developers - because an integer cannot be NULL in most sane programming languages - that is just not how computers work.

share|improve this answer

protected by Jack Douglas Jun 3 '15 at 20:45

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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