A Practical Guide to NULL in SQL Databases

NULL represents missing data in SQL databases. Though it sounds simple, handling NULL in queries, partitions, and indexes requires a thoughtful approach. This article provides a brief guide to managing NULL in your database.

Concepts and examples

NULL as default column value

It’s common to set NULL as the default value in columns. This allows for flexibility when inserting data.

CREATE TABLE example_table (
  column_1 INT DEFAULT NULL
);

Querying for NULL

Since NULL represents the absence of a value, it can’t be queried using = NULL. Instead, SQL requires you to use IS NULL to find NULL values.

SELECT * 
FROM example_table 
WHERE column_1 IS NULL;

NULL in auto-increment columns

When you insert NULL into an auto-increment column, SQL doesn’t store NULL. Instead, it generates the next sequential number automatically.

INSERT INTO example_table (column_1) VALUES (NULL);

NULL and partitioning

Handling NULL values in partitions requires special attention. MySQL places NULL in the "lowest" partition when using RANGE partitioning. LIST partitions require NULL to be explicitly listed.

FAQ

How does NULL differ from an empty string?

An empty string is a valid value. NULL means "no value," and it requires special handling in queries and conditions.

Does NULL increase storage usage?

Yes, but only slightly. NDB storage engines reserve 4 bytes per NULL value.

Can NULL be indexed?

Yes, indexes can include NULLs. But using NOT NULL constraints reduces storage usage.

What happens to NULL in partitions?

In RANGE partitions, NULL goes to the lowest partition. For LIST partitions, NULL must be listed explicitly in the partition definition.

Conclusion

NULL values are a simple but sometimes misunderstood concept. From queries to partitioning, knowing how to handle NULL makes database operations smoother. For a more detailed guide, check out the article Working with NULL in Databases: Turn Your Frustration Into Delight.