Nov 07, 2022 by Robert Gravelle
Back in 2020, we learned about The NULL Value and its Purpose in Relational Database Systems. As stated in that article, the value NULL has become a special marker to mean that no value exists. You could also say that NULL values may indicate that a column could have a value, but you don't know what that value should be yet. In that context, they act as a placeholder until you finally collect the data needed to fill the table field with a real value.
Moreover, when you consider that all major database vendors support NULLs as default values, it only makes sense to use them, doesn't it? Well, not so fast. There are database designers who avoid using NULLs unless absolutely necessary. Do they know something that the rest of us don't? Read on to find out!
Although NULL values represent "nothing" or "no value", they are treated as a value by the database. As such, they take up space on the hard drive. So, if you think that you are saving hard drive space by employing NULL values, you could be mistaken. In fact, NULL is considered to be a variable-length value, meaning that it could be a few bytes or several bytes, depending on the column type. The database leaves room for extra bytes should the value be larger than what is stored in the field, the result being that your database might take up more hard drive space than if you had used regular values.
Some database administrators argue that if all the columns of a record can't be filled, then a record shouldn't be created. This argument obviously doesn't apply to all use cases, but the idea behind it is that a record should only be created when all fields have actual values without any placeholders. For example, in a banking application, you wouldn't proceed with a transaction if you didn't know the amount of the transaction. Fair enough, but this type of rigorous standard doesn't work so well in other industries such as e-commerce or websites that collect user data.
Another disadvantage affects your database stored procedures. While most databases provide functions to detect NULL values, special care must still be taken to distinguish NULLs from other values. This means that your SQL procedures might be much longer than necessary, and they can become complex to read as well. A database administrator may reject code changes if the procedures are too convoluted and/or unintelligible.
Case in point, here's a small table in Navicat Premium 16 that contains a combination of values, empty strings, and NULLs:
In Navicat, it's easy to insert an empty string or NULL via the Edit menu.
Now here's a query that counts the number of names based on a variety of criteria:
We were looking for a count of 5 as records 4, 5, 7, 8, and 10 do not have values in them. However, only the combo_count returned 5. This is because while a NULL value does NOT have a length, so NULLs are not picked up by the length() function.
From this example, we can conclude that allowing NULL values can make you work extra hard to get at the kind of data you are looking for. Moreover, allowing NULL values may reduce your confidence regarding the data in your database, as you can never quite be sure whether a value exists or not.
Most database practitioners choose to allow some NULL values in their database tables, as they are the default value in just about any well known database and function well as a placeholder for missing data. On the other hand, we saw here that some DBAs don't feel that NULLs are worth the extra trouble they entail. The moral of this story is that you should consider your own business processes before designing your database(s) and choose a structure that best suits your data.
