Insights

Should You Use NULL values in Your SQL Database?

Barry

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!

×

Membership Level change

You have selected the Search membership level.

The price for membership is £0.00 now.

Create a FREE account to view the data!


Login Details Already have an account? Log in here

LEAVE THIS BLANK

About You

*

Terms and Conditions of use

Terms and Conditions

1. Acceptance the Use Of Doorda.com Terms and Conditions

Your access to and use of Doorda.com (the Website) , the property of Doorda Ltd (“Doorda”), is subject exclusively to these Terms and Conditions. You will not use the Website for any purpose that is unlawful or prohibited by these Terms and Conditions. By using the Website you are fully accepting these including any disclaimers stated therein . If you do not accept these Terms and Conditions you must immediately stop using the Website.

2. Credit card details

Doorda will never ask for Credit Card details and requests that you do not enter it on any of the forms on the Website

3. Advice

The contents of the Website do not constitute advice and should not be relied upon in making or refraining from making, any decision.

4. Non-Commercial Use

All content on the Website is for non-commercial use only and is issued under the Creative Commons Attribution-Non Commercial 4.0 International Licence .

5. Change of Use

Doorda reserves the right to:

(1) Change or remove (temporarily or permanently) the Website or any part of it without notice and you confirm that Doorda shall not be liable to you for any such change or removal
(2) Change these Terms and Conditions at any time, and your continued use of the Website following any changes shall be deemed to be your acceptance of such change.
and
(3) Discontinue access by any user at any time , such action being entirely at Doorda’s discretion

6. Links to Third Party Websites

The Website may include links to third party websites that are controlled and maintained by others. Any link to other websites is not an endorsement of such websites and you acknowledge and agree that we are not responsible for the content or availability of any such sites.

7. Intellectual Property

7.1 All copyright, trademarks and all other intellectual property rights in the Website and its content (including without limitation the Website design, text, graphics and all software and source codes connected with the Website) are owned by or licensed to Doorda or otherwise used by Doorda as permitted by law.

7.2 In accessing the Website you agree that you will access the content solely for your personal, non-commercial use. None of the content may be downloaded, copied, reproduced, transmitted, stored, sold or distributed without the prior written consent of the copyright holder. This excludes the downloading, copying and/or printing of pages of the Website for personal, non-commercial home use only.

8. Disclaimers and Limitation of Liability

8.1 The Website is provided on an AS IS and AS AVAILABLE basis without any representation or endorsement made and without warranty of any kind whether express or implied, including but not limited to the implied warranties of satisfactory quality, fitness for a particular purpose, non-infringement, compatibility, security and accuracy.

8.2 To the extent permitted by law, Doorda will not be liable for any indirect or consequential loss or damage whatever (including without limitation loss of business, opportunity, data, profits) arising out of or in connection with the use of the Website.

8.3 Doorda makes no warranty that the functionality of the Website will be uninterrupted or error free, that defects will be corrected or that the Website or the server that makes it available are free of viruses or anything else which may be harmful or destructive.

8.4 Nothing in these Terms and Conditions shall be construed so as to exclude or limit the liability of Doorda for death or personal injury as a result of the negligence of Doorda or that of its employees or agents.

9. Indemnity

You agree to indemnify and hold Doorda and its employees and agents harmless from and against all liabilities, legal fees, damages, losses, costs and other expenses in relation to any claims or actions brought against Doorda arising out of any breach by you of these Terms and Conditions or other liabilities arising out of your use of this Website.

10. Severance

If any of these Terms and Conditions should be determined to be invalid, illegal or unenforceable for any reason by any court of competent jurisdiction then such Term or Condition shall be severed and the remaining Terms and Conditions shall survive and remain in full force and effect and continue to be binding and enforceable.

11. Governing Law

These Terms and Conditions shall be governed by and construed in accordance with the laws of England and you hereby submit to the exclusive jurisdiction of the English courts.

 Click here to read full terms of use.
Join our mailing list.