DEV Community

Cover image for A Practical Guide to NULL in SQL Databases
DbVisualizer
DbVisualizer

Posted on

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
);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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.

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay