Should You Use NULL values in Your SQL Database?

Should You Use NULL values in your SQL Database? If you search for NULL standards in Google, the first page shows you answers from both sides of the debate. Using NULL values in your database is a permanent choice. Once you choose to allow them, you need to allow NULLs in all SQL database tables. Relational databases include NULLs by default, but they all let you reject NULLs should you decide to require developers to enter a value. Allowing NULL values has been an ongoing debate among database administrators for years.

What is a NULL Value?

One common misconception about NULL values is that they represent “nothing” or “no value.” NULLs are indeed a value. They take up space on your database hard drive as opposed to “nothing” that indicates there is no value. It’s common for new database developers to think that they are saving hard drive space by adding NULL values, but this isn’t necessarily the case.

NULL values are used to indicate that you could have a value, but you don’t know what that value should be yet. They are placeholders until you finally collect the data needed to fill the table field with a real value.

You should never confuse NULL values for zeros or blank strings. This is another misconception. When you see a NULL value in a table, it means that you don’t know the value yet. When you see a zero value, it means that the total or integer value is supposed to be zero. You know that the value should be zero. Think of NULL as “unknown.”

You can use NULL values for any data type including integers, decimals, strings, or blobs. Even though many database administrators use NULL, they usually demand that NULLs are not used for numeric values. The reason is that NULLs used for numeric values can become confusing when developing code to calculate data.

The Advantages of Using NULL Values

While it seems silly to use NULLs, they actually have a good purpose in relational databases. Every major database vendor on the market allows you to insert NULLs by default including Microsoft SQL Server and MySQL. The databases automatically exclude these values when using internal functions.

For instance, suppose you want to add a list of order totals. You have some orders that haven’t completed, and you choose to use NULL values as a placeholder. The database programmer uses the internal SUM function to add all the totals. The function automatically removes the NULL values and doesn’t count them in the calculation. This is especially important when you use the AVG (average) function with your calculations. The database removes them from the calculation and your average is only the records with values. If you use a zero value, the function would include them in your calculations and skew results.

Programming languages outside of database SQL also have functions that use NULL. For instance, if you program in the Microsoft NET framework or the Linux-based PHP language, you have functions readily available to you that let you evaluate values for NULL and use them to create logical loops and structures. This is common when you have string values that have NULL as a placeholder. When your program sees a NULL value, you know that there is no real value and you can skip the record or display specific text to your users that a value hasn’t been stored yet. The logic you use for NULLs is your decision, but you have readily available functions that make it much easier to identify a placeholder value versus a real value.

If you used zero values, your programs wouldn’t know the difference between a user entering a zero or the database storing zero as a placeholder. This is one reason advocates for NULL database values prefer it.

With programming, having NULLs in your database improves the logic. You don’t need to account for several different possibilities for placeholder values. You just test for NULL and then use your logic to display content to your users. If you had several different placeholders, you’d need to write code for each one.

Another advantage is that databases allow you to automatically enter NULL into a field when no value is available. This means that you don’t need to figure out a value for each placeholder. You just use NULL and every database programmer recognizes it as the default placeholder.

The Disadvantages of NULL

With all of its advantages, NULL also has disadvantages that database administrators use to argue against using NULLs in any table.

The first one is that NULL is considered a variable-length value. This means that it could be a few bytes or several bytes. The database leaves room for extra bytes should the value be larger than what is stored in the field. The result is that your database might take up more hard drive storage space than if you used regular values.

Database administrators also argue that if all values can’t be filled, then a record shouldn’t be created. This argument doesn’t always work for all environments, but the idea is that a record should only be created when all fields have actual values without any placeholders. For instance, you wouldn’t want to allow a bank transaction to happen if you don’t know the amount of the transaction. This standard works in the financial industry, but it doesn’t work well in other industries such as e-commerce or websites that collect user data.

Another disadvantage is in your database programming procedures. While you can use functions that automatically detect NULL values, custom functions must be created to eliminate NULLs. This means that your SQL procedures might be much longer than necessary, and they can be too complex to read. A database administrator will reject code changes if the procedures are too convoluted and messy.

Should You Use NULL Values?

Now that you’ve seen the advantages and disadvantages, you must decide if you’ll allow NULL values. There are several more advantages than disadvantages. Hard drive space is much cheaper than it was a few decades ago, so using more hard drive space isn’t a good argument. Having cleaner code is much better than having complex code, and you can streamline it when you have NULL values as a default.

NULLs are widely recognized by desktop, web, and database programmers. You don’t need to explain the reasons for them like you do with custom placeholders. They are standards in the industry even with the widespread debate.

If you decide to use NULLs, always use them uniformly across all of your database tables. Every table must allow them, and you should never use multiple placeholders in different tables. This can lead to bugs in your applications.

Take a look at DoordaHost

How did we learn so much about Optimizing SQL? By creating our own hosted data solution! DoordaHost allows you to interrogate our data products in situ, this includes information on all UK addresses via  DoordaProperty and data on over 12 million organisations held in DoordaBiz. We do all the data processing all you need to do is query and extract!