In When to use NULLable columns in relational databases I admitted that sometimes using NULL
in a relational database is desirable. But in What does NULL mean in SQL? I showed that NULL
is implemented inconsistently in SQL, mixing two different meanings. Also, columns are NULL
able by default, which shouldn’t be the case.
I would say that many languages have similar problems. Tony Hoare invented null references for the ALGOL W language in 1965, and in 2009 he apologised for that. See the video. Of course null references are something different from SQL NULL
, but if you think about it they cause similar problems (with different consequences).

seems to be made with Rust
The Rust solution
I’m learning Rust, basically because I’m tired of interpreted languages. This has nothing to do with performance. The speed of my code itself usually doesn’t matter too much when I automate stuff, because execution time is consumed by activities that my code doesn’t control. But I’m tired of loose types and difficulties when in need to deploy on some environments (for example: python 2 and 3 are not fully compatible, and none of them is present on all servers).
Apparently, Rust is not the simplest compiled language one can use. But once you understand why it implemented certain concepts, they become simpler and more pleasant to deal with. (Disclaimer: as a Rust newbie, I could be completely wrong) Rust basically tries to make our code as safe as possible, eliminating sources of risks. And an example of risk that Rust avoids is… NULL
.
Rust doesn’t have NULL
. But it still gives you the ability of having variables without a value, which is very important to program if you are not omniscient. The core idea of Rust’s preudo-NULL
is:
If you have a variables whose value could be absent, you cannot treat it as if it surely had a value.
This about it. This solves all problems. Really.
Rust Option
Rust’s solution is an enum called Option
.
An enum is what you would expect if you know some programming languages, or MySQL non-standard types. When you define an enum, you list the values it can have. Wherever in the code that enum is expected, one of the listed values must be passed.
But there is one more thing. In Rust, an enum value can have parameters.
Let’s not dig too much into Rust enums, let’s just see the particular enum definition that we are interested in:
enum Option<T> {
None,
Some(T)
}
This means that when we create a variable of type Option
we’ll also specify the type of its parameter. And then we can use None
, which is Rust equivalent of NULL
, or Some
with an actual value passed as a parameter.
Why they called it None
instead of NULL
I don’t know, but after all Python preferred this wording too.
For example:
-- procedural SQL, MySQL syntax
DECLARE name1 TEXT DEFAULT NULL;
DECLARE name2 TEXT DEFAULT 'Greta';
// Rust equivalent
let name1: Option<String> = None;
let name2: Option<String> = Some("Greta".to_string());
At this point you may wonder: what if we declare a variable and don’t assign it a value, and then we try to use it? The answer is simple: our code won’t compile.
Using Options
At a first glance, Option
looks like a uselessly verbose way to do the same things that NULL
can do. That’s not the case.
First of all, Rust is strongly typed. We cannot use None
unless a variable was declared as Option
.
Secondly, we cannot just forget it is an Option
and use it as a primitive type. If a variable is Option(<String>)
you can assign it Some("Greta")
, but not just "Greta"
. Doing so would simply result in a compiler error.
The third reason is a consequence of the second: to use the actual value, we are forced to use an Option
method (associated function, in Rust jargon). For example:
- procedural SQL, MySQL syntax
DECLARE n TINYINT UNSIGNED DEFAULT NULL;
-- result: NULL
SELECT n + 1 AS result;
// Rust equivalent
let n: Option<u8> = None;
// returns 1
n.unwrap_or(0) + 1
The SQL example is a demonstration of how we may get NULL
because we write an expression that involves a NULL
. The Rust example shows how this cannot happen in Rust; instead, we are forced to decide what a variable will be treated if it’s None
.
Finally, it’s worth mentioning that in Rust we can still produce a binary that panicks if a certain statements involves a None
. But it’s our choice, it means that we want the program to crash if that happens. Here’s how:
let name: Option<String> = None;
// calling None.unwrap() will cause the program to panick
println!("Hi, {}!", name.unwrap());
Here’s what I think that SQL should learn from Rust, in this respect.
- It should not be possible to use the standard syntax for a regular value (
1
,'abc'
,DATE'1994-01-01'
…) in a context whereNULL
is allowed. - Instead, a syntax that specifies what to do with
NULL
should be used in these cases. exists and therefore can be returned, but it shouldn’t be possible to do it by mistake. - Nothing should be
NULL
able by default. Columns should beNOT NULL
unless declared asNULL
. By default, variables shouldn’t acceptNULL
and when not assigned they shouldn’t be readable.
Would adding a new syntax be too verbose for the purpose of SQL? I think not, because NULL
should be an exception that we don’t use often. Most queries shouldn’t have to worry about NULL
at all.
This doesn’t solve one problem: the inconsistency of NULL
semantics in SQL, which I described in What does NULL mean in SQL?. For Rust, this problem doesn’t exist: using None
in an expression will usually cause the program to panick, which always makes sense, no matter if you consider None
as an unknown value or a missing value. In SQL this is not desirable, so it would be desirable to have different markers with different behaviours.
Conclusions
My criticism against NULL
may occasionally sound abstract, because I admit that it makes sense in a few cases, and I never indicated a valid alternative. But now I am using Rust, and I found a very good alternative, which relational DBMSs should use in my opinion.
If you disagree, please drop a comment and explain why. I like to hear different opinions. If you found a mistake in this article, please let me know with a comment. And in both cases, thank you in advance!
Toodle pip,
Federico