NULL

NULL is not zero, and not an empty string

NULL means no value here. Not zero, not "", not false — unknown. Two customers in this database have no city, because nobody ever wrote one down. That is different from a customer whose city is the empty string, which would mean somebody typed nothing on purpose.

Everything strange about NULL follows from one rule: an unknown compared to anything is unknown, and a where clause only keeps rows whose condition is true. Not false — not true.

none of these are true

null = null      →  null      -- two unknowns are not known to be equal
null <> 'Baku'   →  null
null + 1         →  null
'a' || null      →  null      -- one unknown poisons the expression

The bug this causes, in every codebase

Nobody reports this bug. The report is simply short by two, every month, until somebody counts by hand. If a column can be NULL, every negative condition on it needs to say what should happen to the unknowns.

twelve customers, four in Baku — this returns six

select name, city from customers where city <> 'Baku';   -- 6 rows

-- the two with no city answered "unknown", so they were dropped
select name, city from customers
where  city <> 'Baku' or city is null;                   -- 8 rows

is null, and the operators that handle it

the safe set

where city is null
where city is not null

coalesce(city, 'unknown')     -- the first argument that is not null
ifnull(city, 'unknown')       -- SQLite/MySQL, two arguments only
nullif(city, '')              -- turns '' back into null: useful on import
city is not distinct from ?   -- "equal, and two nulls count as equal" (Postgres)

coalesce takes a list

coalesce(mobile, landline, office, 'no number') returns the first one that exists. It is the SQL version of a ?? b ?? c.

not in, and the query that returns nothing

x not in (1, 2, null) asks "is x different from 1, and from 2, and from null?" — and the last one is unknown, so the whole thing is unknown, so nothing comes back. not exists asks a yes/no question about rows instead, and cannot be poisoned. Prefer it.

the sharpest edge in SQL

-- if ANY row of the subquery is null, this returns no rows at all
select name from doors
where  id not in (select door_id from order_items where qty > 100);

-- exists is immune, and usually faster
select name from doors d
where  not exists (select 1 from order_items i where i.door_id = d.id and i.qty > 100);

Aggregates and sorting quietly disagree with you

what NULL does elsewhere

count(*)        -- counts rows
count(city)     -- counts rows where city is not null   → 10, not 12
avg(price)      -- ignores nulls: the average of what exists
sum(hours)      -- ignores nulls; sum of nothing is null, not 0

order by city               -- SQLite/MySQL: nulls first ascending
order by city nulls last    -- the standard, and what you usually mean
order by city is null, city -- works everywhere, including MySQL

A unique column can hold many NULLs

Unknown is never equal to unknown, so a unique constraint does not stop a second NULL. If a column must be filled in, say not null — that is a different promise from unique.

So should a column allow NULL?

Default to not null. Allow NULL when missing is a real state of the world with its own meaning: a job with no fitter assigned yet, a customer who has not told you their city. Do not use it as a stand-in for zero, for "false", or for an empty list — those have values.