Using NULLs in your database is an everlasting choice. Once you’ve decided to allow them, you have to permit NULLs in all database tables. Relational databases comprise NULLs by default; however, they all allow you to reject NULLs if you decide to necessitate developers to enter a value. Allowing NULL values has always been a perpetual debate among database admins.
What is a NULL Value?
One ordinary misinterpretation regarding NULL values is that they delineate “nothing” or “no value.” Contrarily, NULLs are certainly a value. They occupy space on the database hard drive instead of “nothing” that designates there is no value.
It’s typical for new database developers to conceive that they are saving hard drive space by affixing NULL values; however, this isn’t definitive. NULL values are utilized to illustrate that you could have a value, although you don’t know what that value is going to be yet. They are merely placeholders until you ultimately have accumulated the data required to permeate the table field with a real value.
One ought to never muddle NULL values for zeroes for blank strings. Hence, this brings about another misconstruction. When one perceives a NULL value in a table, it signifies that he lacks the knowledge of what the value is yet.
When he perceives a zero value, it indicates that the total or integer value is presumed to be zero. He knows that the value should be zero. Conceive NULL as “unknown.”
You can deploy NULL values for every data type such as integers, strings, blobs, and decimals. Despite that numerous database admins deploy NULL, they generally dictate that NULLs are not employed for numeric values. The rationality is that NULLs utilized for numeric values can become perplexing when developing code to compute data.
Advantages Of Using NULL Values
While it appears to be foolish to use NULLs, they, in reality, possess great purpose in relational databases. All major database vendors on the market permit you to insert NULLs by default notably Oracle and MySQL. The databases automatically preclude these values when utilizing internal functions.
For example, assume you want to implement a list of order totals. There are some orders that haven’t completed yet which also means that you have made the decision to use NULL values as a placeholder.
The database programmer deploys the internal SUM function to compute all the totals. The function spontaneously eliminates the NULL values and does not count them in the computation. This is particularly substantial when you utilize the AVG (average) function with the calculations. The database gets rid of them from the calculation, and your average is merely the records with values. If you employ a zero value, the function would comprise them in your calculations and skew results.
Programming languages that revolve outside of database SQL also hold functions that use NULL. As an example, if you code in the Microsoft NET framework or the Linux-based PHP language, you hold functions that are easily accessible to you that permit you to assess values for NULL and employ them to make logical loops and structures. This is recurrent when you possess string values that have NULL as a placeholder.
When the program perceives a NULL value, you will grasp that there is no real value and you may skip the record or show particular text to your users that a value hasn’t been stored just yet.
The reasoning you apply for NULLs is your choice. You hold easily accessible functions that make it a whole lot easier to pinpoint a placeholder value as opposed to a real value.
If you deployed zero values, your programs wouldn’t be able to distinguish between a user entering a zero or the database accumulating zero as a placeholder. This is one basis champions for NULL database values prefer it.
Having NULLs in the database while coding enhances the logic. One does not require to account for numerous distinctive possibilities for placeholder values. You merely test for NULL and then utilize your logic to illustrate content to your users. If you had various distinct placeholders, you would have to write code for each one.
Another advantage relates to the fact that databases enable you to spontaneously enter NULL into a field when no value is accessible. This signifies that you don’t need to find out a value for every placeholder. Instead, you just have to deploy NULL, and all database coders recognize it as the default placeholder.
Disadvantages Of Using NULL Values
Remember that when something has advantages, disadvantages will inevitably also exist.
In the case of NULL, it’s imperative to discuss the disadvantages since database admins use them to dispute against using NULLs in any table.
The first disadvantage refers to the fact that NULL is perceived as a variable length value. This indicates that it may be a few bytes or many bytes. The database leaves room for additional bytes should the value be substantial than what is kept in the field. The outcome is that the database may use up more hard drive storage space than if you deployed regular values.
Database admins also dispute that if every value cannot be filled, then a record should not be created. This quarrel is not invariably effective for all environments, but the notion is that a record should merely be made when all fields have real values without any placeholders. For example, no one would tolerate a bank transaction to occur if he doesn’t know the amount of the transaction. This expectation works in the financial industry, but it is ineffective in other industries like e-commerce.
Another disadvantage of NULL refers to your database coding strategies. While one may utilize functions that automatically discover NULL values, custom functions ought to be created to remove NULLs. This indicates that the SQL strategies may be much longer than necessary, and they can be too complicated to read correctly. A database admin will deny code changes if the strategies used are too tangled.
Should You Use NULL?
If you’ve read up to this point, you will have understood the advantages and disadvantages of using NULL in your database. This is the time to make the decision whether you will permit NULL values or not.
There are many more advantages than disadvantages. In our modern times, it is easy to obtain more hard drive space since prices have plummeted significantly in contrast to many decades ago. Therefore, the argument pertaining the usage of more hard drive space is feeble and futile.
Having cleaner code will always be significantly better than having complicated code. One may streamline it when there are default NULL values in place.
NULLs are broadly acknowledged by desktop, database, and web coders. There isn’t a need to elucidate the reasons for them like one would need with custom placeholders. They are expected in the industry despite the ongoing disputation. Hence, they serve as standards.
If you make the decision to use NULLs, always remember to use them consistently across all of your database tables. Every table has to allow them, and you should never employ various placeholders in different tables. This may result in bugs within your applications.