NULLs Are Not The Same – A Guide

In this blog, we’re walking you through the upsides and downsides in NULLs in MySQL – have a read!

---

Tools used in this tutorial

DbVisualizer, top rated database management tool and SQL client
The MySQL database version 8 or later

---

Everyone has heard about NULL values – one can hardly find a DBA or any developer that didn’t make use or seen such values in his work. Part of that is because NULL values signify the absence of something – but another part of it may be attributed to the fact that we only see the tip of the iceberg when it comes to them. In this blog, we will tell you everything you need to know about NULL values in MySQL and beyond.

What Are NULL Values?

NULL values are just what you think they are – a string signifying nothing existing in a column. NULL values are frequently implemented into any table in a very simple and straightforward manner – a query like so will do:

1 CREATE TABLE demo_table (
2 `column_1` VARCHAR(25) [NOT] NULL [DEFAULT…]
3 );

NULL Values when Creating a Table.

Such a query can be modified to let a table know that whenever any data is inserted into that column, it should or shouldn’t be NULL with a default value of X (the default value can also be NULL which is beneficial in some cases.)

You get it – NULL values exist to signify a user that no value exists in a column. As easy as that. Don’t confuse it with the column being empty though – empty values and NULL values are not the same as you will soon find out.

How to Work with NULL Values?

Fortunately or not, all developers and DBAs will encounter NULL values during some course of their work – and for that, they must know how to properly work with those values. It is wise to follow a couple of key rules:

• NULL means false.
• NULL values can be specified as the default value of a column and there’s nothing wrong with that – such a practice would even help DBAs to search for values in a column later on.
• NULL values can be searched for with the IS NULL or IS NOT NULL operators.
• NULL means “a missing value which is unknown to the DBMS”, not “nothing.”
• Comparison operators like =, <=, >= or similar cannot be used to search for NULL values.
• Partitions treat NULL values differently than other values.

Keeping these things in mind, we can move further. The first thing you need to know is that you cannot compare NULL values since all and any comparisons with NULL will equal to NULL. Thus, getting a valid result is simply impossible.

Second very important thing to note that users who search for NULL should search for such values without comparison or equality operators and instead, use IS NULL or IS NOT NULL operators.

NULL values can however be very beneficial for those who work with analytical or other data because employing them together with default values (e.g. specifying the default value of a column to be NULL instead of some other value) can help users save time and know what to expect once data is inserted into their database – if no data would be inserted, the results of the column would be NULL and queries like LOAD DATA INFILE would be significantly faster on columns having the NULL value if we compare those kinds of queries with queries like UPDATE that we would need to run after inserting the data itself.

Those who work with partitions and need NULL values should be vary of the fact that MySQL does not prevent users from using NULL as part of a partitioning expression, yet all of the values containing NULL will always be inserted into the lowest partition possible. We won’t get into the nitty-gritty detail around this, but those who are interested in the specifics should have a read through How MySQL Partitioning Handles NULL over in the documentation.

NULL does not equal “nothing” and contrary to a popular belief, NULL queries will occupy data on the disk, so if you’re concerned about that, please set the default value of your columns to be empty (“”) or NOT NULL.

Other Things to Know

Aforementioned things are pretty much everything you need to know about NULL values in a couple of paragraphs – however, there are other things you should do to empower your database instances, no matter which kind of DBMS you find yourself running.

One of those things is the usage of SQL clients like DbVisualizer – as a SQL client, DbVisualizer is used by notable companies such as NASA, Google, Tesla, Saab and others, and it can help you work with any database management system you desire. With its powerful features each crafted to be able to solve the most pressing real-world database problems faced by developers and DBAs alike, DbVisualizer is a great choice for CEOs, team leaders, or engineers alike.

Have a look through the features provided by the tool, then grab an evaluation trial – you will not be disappointed.

Summary

NULL values in various database management systems and especially MySQL are treated differently – partitions insert them into the lowest partition possible, equality operations are not possible, and nothing isn’t the same as NULL either – with that said, NULL values can be very beneficial in some cases: wield their sword powerfully enough, and you will surely benefit from all of the upsides they provide to DBAs and developers alike.

We hope that this blog has been informational and that you will explore our blog for more information in the future, and until next time!

About the author

Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.