NULL is a special marker in databases indicating a "missing value." While it may seem simple, it introduces unique challenges in queries, indexes, and partitions. This guide covers its key concepts, examples, and usage tips.
Core concepts and examples
NULL as default value
When working with tables, it’s common to set NULL as a default value for certain columns. Here’s how you can do it using SQL.
CREATE TABLE example_table (
column_1 INT DEFAULT NULL
);
Querying NULL values
Querying NULL values requires special syntax. Unlike other values, NULL can’t be found using =. You need to use IS NULL to retrieve rows with NULL values.
SELECT *
FROM example_table
WHERE column_1 IS NULL;
Handling auto-increment columns
When NULL is inserted into an auto-increment column, it behaves differently. Instead of staying NULL, the system generates the next sequential value.
INSERT INTO example_table (column_1) VALUES (NULL);
Partitioning with NULL
Partitioning tables with NULL values can be challenging. MySQL has special rules about how NULL is placed in partitions. Understanding this can help avoid partition errors.
FAQ
How is NULL different from an empty string?
A NULL means "no value," while an empty string is a defined value. This difference matters in query logic and comparisons.
Does NULL affect storage space?
In most cases, no. But storage engines like NDB reserve 4 bytes for each NULL value.
Are NULL values indexed?
Yes, NULLs can be indexed like other values. However, NOT NULL columns use less disk space, so specify them where possible.
How do NULL values affect partitioning?
When partitioning by RANGE, NULL values go into the lowest partition. For LIST partitions, NULL must be listed as a valid option.
Conclusion
NULL values signify missing data, but they affect queries, partitions, and storage in unique ways. Understanding these behaviors helps avoid confusion and improve database performance. For a more comprehensive look, check out the article Working with NULL in Databases: Turn Your Frustration Into Delight.