A short while ago, I gave this answer on Quora. The question was “What is the significance of NULL in SQL?” and most of the existing answers went on about citing C.J. Date or Tony Hoare and unanimously declared NULL as “evil”.
So, everyone rants about NULL all the time. Let me counter-rant.
Academics
Of course, academics like C.J. Date will rant about NULL (see Greg Kemnitz’s interesting answer on Quora). Let me remind you that C.J. Date also ranted about UNION ALL, as pure relational theory operates only on sets, not on bags (like SQL does). While in theory, sets are probably much purer than bags, in practice, bags are just very useful.
These people probably also still mourn over the fact that SQL (useful) won over QUEL (pure), and I don’t blame them. Theory is always more beautiful than the real world, which is exposed to real world requirements.
Purists
There are also other kinds of purists who will run about and educate everyone about their black/white opinions that leave no room to “it depends…” pragmatic approaches. I like to display this witty comic strip for such occasions: New intern knows best: GOTO. Purists like extreme abstraction when they describe their world, and such abstraction asks for very simple models, no complexity. NULL adds tremendous complexity to the SQL “model”, and does thus not fit their view.
Fact is: It depends
The only factual opinion ever is one where there’s no clear opinion. NULL is an incredibly useful value, and some representation of NULL is inevitable in all languages / models that want to model cardinalities of the form:
0 or 1(here’s where NULL is useful)exactly 1(here, you don’t need NULL)0 .. many(here, you don’t need NULL)
Functional programming languages like to make use of the Optional “monad” (see Mario Fusco’s excellent explanation of what a monad is) to model the 0 or 1 cardinality, but that’s just another way of modelling NULL. The (possibly) absent value. Perhaps, if you like to discuss style (then you should read this), NULL vs. Optional may matter to you, but they’re really exactly the same thing. We’ve just been shifting whitespace and curly braces.
The only way to really do without the absent value would be to disallow the optional cardinality and use 0 .. many instead, which would be much less descriptive.
So, regardless of what purists or academics say about a perfect world, we engineers need potent tools that help us get our work done, and NULL (or “Optional” is one of these potent tools that allow us to do so.
Caveat: SQL NULL is not an absent value
Now, the caveat with SQL’s NULL is that it doesn’t behave like an absent value. It is the UNKNOWN value as others have also explained. This subtle difference has severe impact on a variety of operations and predicates, which do not behave very intuitively if you’re not aware of this distinction. Some examples (and there are many many more):
- Indexing NULL With the Oracle Database
- SQL incompatibilities: NOT IN and NULL values
- Row value expressions and the NULL predicate
Even with this specification of SQL NULL being UNKNOWN, most people abuse SQL NULL to model the absent value instead, which works just nicely in most cases until you run into a caveat. It turns out that the UNKNOWN value is even more useful than the absent value, as it allows for modelling things with even more descriptiveness. One might think that having two “special” values would solve problems, like JavaScript, which distinguishes between null (UNKNOWN) and undefined (absent).
JavaScript itself is a beacon of usefulness that is inversely proportional to its purity or beauty, so long story short:
Pick your favourite spot on the useful <-> pure scale
Programming, languages, data models are always a tradeoff between purity and usefulness. Pick your favourite spot on that scale, but stop ranting about NULL being evil. Or as Simon Peyton Jones said:
Haskell is useless
| Reference: | NULL is Not The Billion Dollar Mistake. A Counter-Rant from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
Maybe way deep down null and optional are the “same thing, but unless you have a null-safe language (where Int cannot be null, but Int? can be, for example), they are only same because they serve the same purpose. But it’s more than “shifting whitespace and curly braces”. In unsafe languages, you never know when something might be null. Even documentation can be wrong. Optional makes it explicit that can be “null” as well as making certain you don’t use it in a way that may throw a NullPointerException. Null itself pretends to be an object that you have to… Read more »
I won’t accept the “even documentation can be wrong” argument. I’ve worked in enough project where nullability was a very intuitive treat based on the generally agreed design of the application. It’s true that Option/Optional communicates things much more clearly, I don’t deny that, but it’s possible to screw up generics just as much as it’s possible to screw up design and documentation. Of course, I agree as well that more language sugar for null would be useful, such as Int vs. Int?. In Java, this ship has sailed (much like the Optional ship has sailed btw, unless we deprecate… Read more »