NULLs Are Not The Same – A Guide
Tags: mysql, null
Published: 2023-11-13 08:00:00

**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](https://www.dbvis.com/download/), top rated database management tool and SQL client
The [MySQL](https://dev.mysql.com/downloads/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 );
```

<br />
![NULL Values when Creating a Table.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uqur44vpbitw1k64wskv.png)
<figure><figcaption>NULL Values when Creating a Table.</figcaption></figure>
<br />

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](https://dev.mysql.com/doc/refman/5.7/en/partitioning-handling-nulls.html) 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](https://www.dbvis.com/features/), then [grab an evaluation trial](https://www.dbvis.com/eval/) – 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](https://www.dbvis.com/thetable/) 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.