Understanding Nothing (or NULL) in Fauna

This is the first post in an occasional blog series answering questions submitted by the developer community. Here, Fauna Senior Core Engineer John Miller, III, offers practical examples to help you understand the definition and benefits of NULL in Fauna compared to SQL. Want to know more about something in Fauna? Tweet @fauna to connect with the team and get your questions answered.

In Fauna, null (or NULL), is a special marker used to indicate that a data value does not exist in the database. It is a representation of "missing information". This should not be confused with a value of zero. A null value indicates a lack of a value. A lack of a value is not the same thing as a value of zero, in the same way that a lack of an answer is not the same thing as an answer of "no".

For example, consider the question: "How many cars does Brandon own?" The answer may be "zero" (we know that he owns none) or "null" (we do not know how many he owns). SQL null is analogous to a state rather than a value. This usage is quite different from most programming languages where null typically means missing or not pointing to any object.

Fauna use of NULL is similar to the traditional programmers' use of null, so it doesn't carry the baggage of the SQL implementation of null.

Fauna treats null as a value that can be directly compared for application programmer simplicity. This means that NULL == NULL returns true. In a SQL databases NULL == NULL returns false, as NULL compared to anything is never true, it is unknown. In order to search for the null value in SQL databases one must use a special comparator “IS NULL” or “IS NOT NULL”. This is often the source of many programmer errors in SQL, especially when aggregating or combining rows. Fauna is friendlier to the developer and allows the comparison directly to null as most modern programming languages allow.

Exploring a few practical examples, a Fauna developer might see improvements when dealing with a Fauna-style null value as compared to SQL-style null. A value in Fauna which does not exist in a document or index has a value of null. While SQL generally initializes all values to null, it does not have the concept of non-existent values.

When sorting data in Fauna, null values handling is simple in that nulls come either first or last depending on an ascending or descending ordering requested by the programmer. In SQL when ordering data, nulls always appear at one end of the sorted results set. Inverting the sorted order (i.e. ascending to descending) does not change where nulls are returned. In order to know which side of the results set null values are returned, a programmer must understand which database vendor they are dealing with or utilize special compensation syntax offered by some database vendors for sorting first or last.

In Fauna, if you create a unique index on a value that does not exist, then the non-existent value will have a value of null. This allows only a single unknown state for the value. In SQL you may have multiple unknown states for the same value. In our example above, there should only exist a single record for the number of unknown cars for Brandon.

The use of the null value in Fauna was designed to be more intuitive to the general programmer. The lack of requiring special code for dealing with nulls should reduce the number of errors that traditional SQL developers encounter when not anticipating the use of nulls and omitting the special code to handle nulls. The experience for the application programmer when using Fauna should be more intuitive and productive, and free from many special cases.