NULL in SQL Explained: Why = NULL Never Works

NULL is how SQL represents a missing or unknown value. It's not zero, not an empty string, not false. It's "we don't know". And that leads to the most common SQL surprise of all:

SELECT * FROM users WHERE phone = NULL;   -- always returns nothing

Why = NULL doesn't work

SQL treats NULL as unknown. Is an unknown value equal to another unknown value? Unknown. So phone = NULL is neither true nor false — it's NULL — and WHERE only keeps rows where the condition is true.

Even NULL = NULL is NULL, not true.

Use IS NULL and IS NOT NULL

SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

ORMs generate this for you when you filter on null, but raw SQL won't.

NULL vs empty string vs zero

Value	Means
NULL	Unknown / not provided
''	Known to be empty
0	Known to be zero

A user who left "phone" blank on a form might be stored as NULL or '' depending on your code — and then queries for one miss the other. Pick one convention (usually NULL for "not provided") and stick to it.

NULL spreads through calculations

Almost any operation with NULL gives NULL:

SELECT 10 + NULL;          -- NULL
SELECT 'Hello ' || NULL;   -- NULL

A total that suddenly shows as empty often means one value in the calculation was NULL.

COALESCE: use a fallback

COALESCE returns the first non-NULL value:

SELECT name, COALESCE(nickname, name) AS display_name FROM users;
SELECT COALESCE(discount, 0) + price FROM items;

NULLIF: turn a value into NULL

SELECT total / NULLIF(quantity, 0) FROM line_items;  -- avoids divide-by-zero

Comparing values that might be NULL

To treat two NULLs as equal, use IS DISTINCT FROM / IS NOT DISTINCT FROM:

WHERE old_email IS DISTINCT FROM new_email   -- true if they differ, NULLs included

How NULL affects aggregates

• COUNT(*) counts all rows.
• COUNT(column) skips NULLs.
• SUM, AVG, MIN, MAX ignore NULLs. AVG(rating) averages only rated items — which may or may not be what you want.
• SUM of a column that's all NULL is NULL, not 0. Wrap it: COALESCE(SUM(total), 0).

(SQL GROUP BY)

The NOT IN trap

This one causes real bugs:

SELECT * FROM users
WHERE id NOT IN (SELECT referred_by FROM users);

If any referred_by is NULL, this returns no rows at all, because "is 5 not in (1, 2, NULL)?" is unknown. Use NOT EXISTS instead, which handles NULL correctly:

SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM users r WHERE r.referred_by = u.id);

NULL and unique constraints

By default in Postgres, a UNIQUE column can contain many NULLs, because NULLs aren't considered equal. Since Postgres 15 you can change that with UNIQUE NULLS NOT DISTINCT. (duplicate key value violates unique constraint)

Sorting

In Postgres, NULLs sort last in ascending order and first in descending. Control it explicitly:

ORDER BY last_login DESC NULLS LAST

Prevent NULLs you don't want

If a value must always exist, say so in the schema:

email text NOT NULL,
status text NOT NULL DEFAULT 'pending'

Then the database rejects missing values instead of storing NULL. Being strict about this early saves a lot of COALESCE later. (How to design your first database)

In JavaScript

SQL NULL usually arrives in your app as null. Reading a property of it gives the classic "Cannot read properties of null" error, and TypeScript warns you with "Object is possibly null".

---

EasySpawn gives your app its own Postgres database and a terminal with Claude Code, handy for tracking down exactly which NULL broke a report. See how it works or join the waitlist.

Related: SQL for Beginners · SQL GROUP BY Explained · WHERE vs HAVING · SQL Joins Explained