The Difference Between Empty and Null

by Ollie Matthews

Lead by Tim Ngwena, DS25 recently stumbled into this topic during a Tableau class and we quickly came to realise that it can be rather confusing.

Let’s see if we can get to some clear definitions…

It is worth pointing out first that the concept of Null is not entirely consistent in every context. For example, in C++ the definition of Null is actually 0. But for now let’s focus on what null and empty mean in databases.

NULL

In a database, null means ‘nothing’, absolutely nothing. Like a vacuum or a void. This means that it does not, in actuality (despite what a column says), have a data type (e.g. string, integer etc.) and no functions can be called on it. E.g. we can’t SUM it or check it’s length with the len() function in Tableau. An error would be raised if we tried. Null is simply the word used to show the absence of any data in that field.

EMPTY

Empty on the other hand looks like nothing but is not actually nothing. Empty is really an empty string, so something like this:

“ ” (without the space)

Therefore empty does have a data type (string) and therefore functions can be called on them. For example if we were to call the len() function on an empty, the function would execute and the result would be ‘0’.

Empty cannot exist with other data types either. For example, an 'empty’ integer would instead contain the value 0.

Hopefully this distinction will not cause too many issues on your data travels, but it is worth knowing the difference.

Avatar

Ollie Matthews

Thu 12 Aug 2021