OracleBlog

"My query isn't returning the right rows" is a problem we all hear often. Upon investigation, I find that the most common cause of a miswritten query is a misunderstanding and/or mishandling of NULLs.

Well, you know what? I've had it. Let's get rid of NULL. There, I said it.

I've written articles explaining NULL before, a couple of times in fact*. So have plenty of other Oracle specialists, in both blogs and books. And it's just not getting through. So let's just get rid of NULLs.

* Links to my previous articles:
NULLs in Oracle
NULL vs Nothing - ANSI SQL is unlike programming languages because NULL is not nothing.

The basic problem is that programmers coming from different backgrounds mistakenly think NULL is nothing. You may think NULL is nothing, you may design your application to treat NULL as nothing, but the problem is that Oracle does not consider NULL to be nothing. NULL is, quite simply, "I don't know."

NULL is not nothing, it's a value. It's a value as real as any other. It's simply an unknown value. So why do we need NULL anyway? Let's just call it UNKNOWN. That should avoid any programmer misconceptions.

Let me give you an example to help clear it up. Let's take a table of employees, and for whatever reason, HR keeps track of everyone's favourite colour. How would you treat the following situations?

1. An employee leaves that section blank.
2. An employee writes "blind since birth - never seen a colour."
3. An employee writes "that's personal" or "what a stupid question."
4. An employee writes "I do not have a favourite colour."

In most cases today, each of these employees would have NULL entered as their favourite colour. But would it be appropriate in all cases? In some cases the employee has a favourite colour, we simply don't know what it is. In at least one case, the employee has no favourite colour (ie "nothing").

What if I asked you if two of these employees had the same favourite colour? You couldn't tell me, could you? What if I asked you if they all had different favourite colours? You can't answer that either.

So what would I do? I'd get rid of NULL and use a value more appropriately named UNKNOWN instead.

"But Robert," some of you are thinking, "What about foreign keys?" You may have a table called colour and employee.colour_id might map to colour.id. UNKNOWN may be appropriate for some of those employees, what about the employee without a favourite colour? We can't put UNKNOWN because we know his favourite colour (ie. none). So what do we do? Create another type called NONE or NOTHING?

No. No we don't. I doubt Oracle would even support that properly anyway. I'll tell you what we'd do. We'd insert "Nothing" into COLOUR. And why not? What's the problem with that?

In more practical terms, think of the "supervisor_id" field in a typical employee table. The supervisor_id column would be self-referential to employee.id. What I'm saying is this:
1. Employees with an unknown supervisor would have supervisor_id = UNKNOWN (which is just a simple re-naming of NULL).
2. There would be a "Nobody" employee. (Make sure not to pay him/her!)
3. Employees with no supervisor (the President, for example) would have a supervisor_id equal to Nobody's id.

Since I'm adding "Nobody" as an employee, would I therefore want to add "Unknown" as an employee too, make the column "NOT NULL", thus avoiding the NULL/UNKNOWN value altogether?

No. No way. Look at those questions I asked above - what if I asked you if two employees had the same boss? If they both had a supervisor_id set to the "Unknown" employee, you'd say "yes," which would be wrong! They might have two completely different supervisors - the point is that we don't know. Which is what NULL/UNKNOWN value is for.

I know I'm not alone in my opinion, but I also know that there is no consensus on this issue. I'm expecting more than a few comments from experienced Oracle experts finding fault with my proposals and/or examples, and backing it up quite convincingly. NULL is no doubt one of the great RDBMS arguments, and while it may be frustrating to work with, I hope it made for interesting reading.