The NULL Value and its Purpose in Relational Database Systems

Mar 3, 2020 by Robert Gravelle

In databases, the NULL value is one that has a very particular meaning. Thus, it is important to understand that a NULL value is different than a zero value or a field that contains spaces. In today’s blog, we’ll explore what the NULL value means and how to work with NULLs in Navicat Premium.

It should be noted that the NULL value is not unique to databases. It’s found in most computer programming languages as well, where it evolved as a built-in constant that had a value of zero. In fact, it’s the character 0 that’s still employed to terminate strings in C. However, over time, it came to mean “nothing”. Specifically, NULL is a pointer to a special bit pattern that represents a NULL. This is what is commonly referred to as a “null pointer”.

In a database, zero is a value which has meaning, so the value NULL became is a special marker to mean that no value exists. In that sense, NULL does not refer to a memory location, as it does for programming languages. In a database, the NULL value indicates a lack of a value, which is not the same thing as a value of zero. To illustrate, consider the question “How many CDs does Rob own?” The answer may be “zero” (0) or NULL. In the latter case, the NULL value could mean that we do not know how many CDs Rob owns. Later, the value might be updated with a numeric vale once we have ascertained how many CDs Rob owns.

Any column that is part of a KEY must not allow NULLs, but for other fields, it’s completely up to you. Including the NOT NULL clause next to your column definitions at table creation time will force the user to include a value for that column in INSERT and UPDATE operations. Otherwise, an error will be thrown, and the operation will fail. Navicat Premium‘s Table Design screen includes a Not null column to designate a field as nullable or non-nullable. Here it is in Navicat 15’s new Dark Mode:

On new fields, the Not null checkbox is automatically deselected.

When writing queries, there are times that you’ll want to filter out rows with NULL values. Other times, you’ll specifically want to retrieve rows that contain NULLs. Here’s how to do both:

Queries can filter out nulls using the IS NOT NULL clause. Here’s an example that excludes films from the Sakila database that contain a NULL original_language_id:

Likewise, you can find out which rows contain a NULL value by dropping the NOT from the NOT NULL clause. Now, only those rows with a NULL original_language_id are returned:

In today’s blog, we explored what the NULL value means and how to work with this special value in relational databases.

Interested in finding out more about Navicat Premium? You can try both for 14 days completely free of charge for evaluation purposes!