Magic NULL

TL;DR

• SQL NULL means "unknown." It does not mean "zero," "false," "not applicable," "deleted," or "empty." Using NULL for these semantics creates three-valued logic: TRUE, FALSE, and NULL.
• Three-valued logic causes silent query bugs: WHERE deleted_at IS NULL works, but WHERE deleted_at != '2026-01-01' silently excludes all NULL rows.
• The anti-pattern: using a single nullable column for multiple meanings, like deleted_at IS NULL = active, deleted_at = timestamp = deleted, deleted_at = sentinel = deactivated.
• Fix: Use separate boolean/enum columns (is_deleted, status). Reserve NULL for genuinely unknown values only.

Introduction

NULL is useful when a value is genuinely missing, unknown, or absent. The anti-pattern appears when one nullable field is made to encode several business states, so the database type no longer explains what a row means and each query must reverse-engineer it.

Mental Model

Treat each column as a contract:

• One semantic meaning: a NULL value should have one documented interpretation for that column.
• Explicit state: use a boolean or status value when the domain has named states such as active, suspended, and deleted.
• Optional value: use NULL when absence itself is meaningful and distinct from zero, false, empty, or unknown.

SQL's three-valued logic is not the main problem; hidden business meanings are. Once a column has multiple meanings, every filter, aggregate, API, and type definition must remember the encoding.

The Problem

It's the first Monday of Q2, and your VP of Sales is staring at a dashboard showing 12% lower active user counts than last month. She escalates to engineering. The data team spends two days investigating before a junior analyst finds it: a month ago, someone added a third meaning to the deleted_at column.

Your team uses a nullable deleted_at timestamp for soft deletion. NULL means active. A timestamp means deleted. This is common and mostly fine.

Then someone adds: if deleted_at is a specific sentinel value (e.g., '1970-01-01'), it means "deactivated by admin but not deleted." Now NULL has one meaning, a real timestamp has another meaning, and the sentinel timestamp has a third meaning. Three values of one column, three interpretations.

The bug: an analytics query filters WHERE deleted_at IS NULL OR deleted_at > '2020-01-01'. This intended to find "active or recently deleted" records. But it silently excludes all deleted_at = '1970-01-01' rows (the deactivated accounts). The analytics report is quietly wrong for weeks before anyone notices.

The recurring root cause is the same: a column carries more semantic weight than its type can express.

The worst part? The analytics team didn't question the numbers for weeks. The 12% drop looked like normal churn. Nobody realized the data was wrong until a sales manager manually counted accounts and got a different number.

The timeline of a magic NULL bug

1. Month 0: deleted_at column added. NULL = active, timestamp = deleted. Clean, simple.
2. Month 6: Admin deactivation feature needed. Developer reuses deleted_at with sentinel '1970-01-01'. No migration, no discussion. "Quick fix."
3. Month 7: Analytics query WHERE deleted_at IS NULL still works for "active users." Nobody notices the deactivated accounts are being ignored in a different report.
4. Month 9: VP notices the numbers are off. Two-day investigation begins.
5. Month 9 + 2 days: Root cause found: three meanings in one column. Now you need to backfill a new status column across 50 million rows.

SQL's three-valued logic

SQL defines three truth values: TRUE, FALSE, and NULL (unknown). The rules feel intuitive until they don't:

SELECT 1 WHERE NULL = NULL;  -- returns 0 rows! NULL is not equal to NULL.
SELECT 1 WHERE NULL IS NULL; -- returns 1 row. Correct test for NULL.
SELECT 1 WHERE NULL != 1;    -- returns 0 rows! Not because it's false,
                              -- but because NULL != 1 evaluates to NULL.

Any comparison involving NULL evaluates to NULL, not TRUE or FALSE. WHERE clauses return only rows where the condition is TRUE, so NULL rows are excluded silently.

This means:

-- This is NOT the complement of WHERE status = 'active'
WHERE status != 'active'
-- It excludes rows where status IS NULL entirely, silently.

The practical consequence is subtle: simple equality or inequality does not cover nullable columns completely. Queries must explicitly handle the NULL case when it is part of the intended result, and forgetting to do so does not raise an error; it silently returns fewer rows.

-- The CORRECT complement requires explicit NULL handling
WHERE status != 'active' OR status IS NULL

Aggregation traps

NULL also affects aggregate functions in non-obvious ways:

-- COUNT(*) counts all rows, but COUNT(column) skips NULLs
SELECT COUNT(*) FROM users;            -- 10,000
SELECT COUNT(deleted_at) FROM users;   -- 3,200 (only non-NULL values)
SELECT COUNT(*) - COUNT(deleted_at);   -- 6,800 (the NULL rows)

-- AVG ignores NULLs entirely
SELECT AVG(score) FROM reviews;
-- If 40% of rows have NULL score, the average is computed
-- over the remaining 60% only. This may or may not be
-- what you want, but it may not match the intended population.

Common NULL overloading mistakes

Before and After

Before, one timestamp carries three states:

-- NULL = active, a real timestamp = deleted, epoch = suspended
deleted_at TIMESTAMP NULL

After, the state and its audit timestamps have separate contracts:

status VARCHAR(20) NOT NULL
  CHECK (status IN ('active', 'suspended', 'deleted')),
deleted_at TIMESTAMP NULL

A query can now ask for status = 'active' without knowing a sentinel encoding, while deleted_at remains useful as the timestamp of a real deletion.

Why It Happens