NULL, and the values that are not there

Unknown is not false

A NULL is a value that was not recorded: an unknown, not a zero and not an empty string. SQL therefore has three truth values rather than two — true, false, and unknown — and a WHERE clause keeps only the rows that come out true. A row whose condition is unknown is dropped exactly as a false one is, silently, which is where the first confusion always comes from.

Comparing with the equals sign cannot find NULLs, because "is unknown equal to unknown" has no true answer. IS NULL and IS NOT NULL are the tests that ask the question properly: WHERE notes IS NULL finds the rows that never had a note.

Two functions worth knowing by heart

COALESCE(a, b, c) returns the first argument that is not NULL — the standard way to say "this, or else that". Totals use it constantly: sum(price_net + COALESCE(discount, 0)) adds a number to a value that might not exist, without the total going NULL in sympathy. The arguments also have to agree on a type: COALESCE(price, 0) is fine because both sides are numeric, while mixing text and a number needs an explicit cast before the call.

NULLIF(a, b) returns NULL when its two arguments are equal, and the first argument otherwise. Its classic job is protecting a division: total / NULLIF(count, 0) answers NULL instead of raising a division-by-zero error — and NULL is the honest answer when the divisor was nothing.

Declaring what may be missing

A column that may not be missing is declared NOT NULL, and the database then refuses to accept a row that leaves it empty — which is a rule enforced for every writer, including tomorrow's. A column with a DEFAULT fills itself in when the INSERT does not mention it: DEFAULT now() for a timestamp, DEFAULT 0 for a counter, DEFAULT false for a flag.

Adding the rule later is its own operation. ALTER TABLE products ALTER COLUMN price SET NOT NULL reads every existing row and refuses the change if one holds NULL, so the error is not a fault in the statement — it is the table saying the data you already have does not meet the promise you are about to make. Fill the gaps first, then add the constraint.

DEFAULT and NOT NULL are the first taste of the real value of a database: it is not storage, it is a set of rules that hold whatever the application does. The next chapter adds the rules that tie tables to each other.

The truth table has a third row

Three-valued logic is easier once you see the rule: a comparison with NULL is unknown, and AND/OR propagate that unknown only when the answer actually depends on it. false AND unknown is false, because a false on either side settles it. true OR unknown is true, for the same reason. Everything else involving unknown stays unknown.

That is why WHERE NOT (discount = 10) does not find the rows with no discount. The comparison is unknown, NOT unknown is still unknown, and WHERE keeps only true. To find the missing values you have to ask about missingness directly, with IS NULL.

Defaults are for omitted columns

A DEFAULT is applied when the INSERT does not mention the column. Writing NULL explicitly is a different act: it stores a missing value and the default never runs. This distinction explains the classic surprise where a column with DEFAULT 0 still contains NULLs — some writer named the column and passed NULL.

NOT NULL is checked on the row that is about to be stored, after defaults are applied. The check is per row and immediate, so a failed insert changes nothing. A default expression such as now() is evaluated for each row in the statement; in a single transaction it returns the same instant for all of them.

Try these next

• Compare count(*), count(rating) and count(DISTINCT rating) on the same table.
• Add a NOT NULL column with a default and insert a row that names the column as NULL.
• Use IS DISTINCT FROM to compare two nullable columns and see how it differs from <>.

Missing values, handled

CREATE TABLE products (
    id integer,
    title text NOT NULL,
    price numeric(10, 2) NOT NULL,
    discount numeric(10, 2),
    notes text,
    added_on date DEFAULT CURRENT_DATE
);

INSERT INTO products (id, title, price, discount, notes) VALUES
    (1, 'Desk', 249.00, 25.00, 'floor model'),
    (2, 'Lamp', 24.50, NULL, NULL),
    (3, 'Chair', 89.00, NULL, 'check stock');

-- This finds nothing: nothing is equal to NULL.
SELECT title FROM products WHERE discount = NULL;

-- This is how the question is asked.
SELECT title FROM products WHERE discount IS NULL;

-- This, or else that — and a division that cannot explode.
SELECT title,
       price - COALESCE(discount, 0) AS net,
       COALESCE(notes, 'no notes') AS note,
       price / NULLIF(discount, 0) AS ratio
FROM products;

-- The default was filled in without being mentioned.
SELECT title, added_on FROM products;