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 the tutorial
Tool Description Link
MySQL logo MYSQL 8.0+

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.
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.


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!

Dbvis download link img
About the author
Lukas Vileikis
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 -, frequently speaks at conferences and blogs in multiple places including his blog over at
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published

MariaDB vs MySQL: The Ultimate Comparison

author Antonello Zanini tags MARIADB MySQL 7 MINS 2023-11-23

SQL Interview Questions (MySQL): Part 1

author Lukas Vileikis tags MySQL 6 MINS 2023-10-25

Working with Numeric Data Types in MySQL: A Comprehensive Guide

author Lukas Vileikis tags MySQL 4 MINS 2023-10-19

Discover DbVisualizer Security Features for MySQL

author Igor Bobriakov tags MySQL SECURITY 6 MINS 2023-10-10

MySQL Operators – A Guide

author Lukas Vileikis tags MySQL OPERATORS 5 MINS 2023-10-05

Your Database Doesn’t Like Your Data – Reserved Words in MySQL

author Lukas Vileikis tags MySQL RESERVED WORDS 5 MINS 2023-09-28

PostgreSQL vs MySQL: The Definitive Comparison

author Antonello Zanini tags MySQL POSTGRESQL 8 MINS 2023-09-08

PostgreSQL ISNULL: The Missing Function

author Antonello Zanini tags NULL POSTGRESQL 5 MINS 2023-09-05

MySQL FAQs Explained: Your Guide to MySQL Facts, Myths, and Optimization Tips

author Lukas Vileikis tags MySQL 6 MINS 2023-08-22

Math Functions in Databases – MySQL Edition

author Lukas Vileikis tags MATH MySQL 4 MINS 2023-08-15

The content provided on, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗