07 · Working with NULL¶

🎥 Video walkthrough¶

NULL represents "unknown" or "absent" — it is not the same as zero, an empty string, or false. It has special comparison rules that trip up almost everyone the first time.

NULL is not equal to anything, including itself¶

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    price REAL,
    discontinued_reason TEXT
);

INSERT INTO books (title, price, discontinued_reason) VALUES
    ('Dune', 9.99, NULL),
    ('Old Title', NULL, 'out of print'),
    ('Another Old One', NULL, NULL);

SELECT * FROM books WHERE price = NULL;    -- returns ZERO rows, always
SELECT * FROM books WHERE price != NULL;   -- also returns ZERO rows, always

Both queries above are silently wrong but produce no error — = NULL and != NULL never match anything because comparing "unknown" to anything, even NULL itself, yields "unknown" (which SQL treats as not-true). This is one of the most common real-world SQL bugs.

IS NULL and IS NOT NULL — the correct check¶

SELECT * FROM books WHERE price IS NULL;

id  title             price  discontinued_reason
--  ----------------  -----  --------------------
2   Old Title         NULL   out of print
3   Another Old One   NULL   NULL

SELECT * FROM books WHERE price IS NOT NULL;

id  title  price  discontinued_reason
--  -----  -----  --------------------
1   Dune   9.99   NULL

COALESCE — first non-NULL value¶

SELECT title, COALESCE(discontinued_reason, 'still in print') AS status
FROM books;

title             status
----------------  --------------
Dune              still in print
Old Title         out of print
Another Old One   still in print

COALESCE(a, b, c, ...) returns the first argument that isn't NULL — the standard way to provide a fallback/default display value.

NULL in arithmetic and aggregates¶

SELECT price + 1 FROM books WHERE title = 'Old Title';   -- NULL, not 1

Any arithmetic expression involving NULL produces NULL — it propagates through calculations rather than being treated as zero.

SELECT COUNT(*) AS all_rows, COUNT(price) AS rows_with_price, AVG(price) AS avg_price
FROM books;

all_rows  rows_with_price  avg_price
--------  ---------------  ---------
3         1                9.99

COUNT(*) counts every row. COUNT(column) and aggregate functions like AVG, SUM, MIN, MAX all silently skip NULL values rather than erroring or counting them as zero — AVG(price) here is 9.99, not 3.33.

NULL in ORDER BY¶

SELECT title, price FROM books ORDER BY price;

In SQLite, NULL sorts first in ascending order (before any real number) and last in descending order. Other databases vary on this default (Postgres also puts NULL last on DESC, but treats it as largest by default on some configurations) — always check your specific engine's docs, or force it explicitly:

SELECT title, price FROM books ORDER BY price IS NULL, price;
-- non-NULL prices first (in ascending order), NULLs pushed to the end

NULLIF — turn a specific value into NULL¶

SELECT title, NULLIF(price, 0) AS price_or_null FROM books;

NULLIF(a, b) returns NULL if a equals b, otherwise returns a — useful for turning sentinel values like 0 or '' into a proper NULL before further processing (e.g. before passing into AVG, so a placeholder zero doesn't wrongly drag down the average).

Cheat sheet¶

Task	Correct approach
Check for NULL	`WHERE col IS NULL` (never `= NULL`)
Check for NOT NULL	`WHERE col IS NOT NULL`
Provide a default	`COALESCE(col, 'default')`
Turn a sentinel into NULL	`NULLIF(col, sentinel_value)`
Count non-NULL values	`COUNT(col)` (not `COUNT(*)`)
NULLs in aggregates	Silently skipped, not treated as 0
NULLs in arithmetic	Any operation with NULL produces NULL

How It Actually Works¶

NULL isn't a value stored in a column — in SQLite's record format, a NULL is represented by a serial type of 0 in the row header, meaning zero payload bytes are stored for that column at all. This is why NULLs are essentially free storage-wise. The reason NULL = NULL evaluates to NULL (not true) is that SQL's comparison operators implement three-valued logic: every comparison against an unknown returns "unknown," not false — the engine literally propagates a NULL marker through the expression tree rather than a boolean. IS NULL / IS NOT NULL are special VDBE opcodes that check the record header's serial type directly rather than performing a value comparison, which is why they're the only reliable way to test for NULL. This three-valued logic also silently affects WHERE, which only keeps rows where the predicate evaluates to true (not just non-false) — a row where the condition evaluates to NULL is filtered out exactly like a false one, which is a common source of "missing rows" bugs when NULLs are involved in NOT IN subqueries.

🔀 See this in another language¶

Exercise¶

Using the books table above: write a query using COALESCE that shows each book's price, defaulting to 0.00 when the price is NULL. Then write a query that counts how many books have a NULL price using IS NULL, and a separate query using NULLIF that treats a price of exactly 0 the same as NULL when computing the average price.