NULL values: meaning, risks, and how to handle them

NULL Is Not Zero or Empty

NULL means unknown or not applicable — it is not zero, not an empty string, and not false. This causes unexpected behavior if you are not careful.

NULL in Comparisons

SQL uses three-valued logic: TRUE, FALSE, or NULL. Any comparison with NULL produces NULL, which evaluates as false in a WHERE clause.

-- This returns NO rows, even if discount IS NULL
SELECT * FROM products WHERE discount = NULL;

-- Correct way
SELECT * FROM products WHERE discount IS NULL;
SELECT * FROM products WHERE discount IS NOT NULL;

-- COALESCE: replace NULL with a default
SELECT name, COALESCE(discount, 0) AS discount FROM products;

NULL vs Empty String

An empty string '' is a known value — "the user submitted a blank field." NULL is the absence of any value — "we never asked." Keep them distinct. Store empty string when the user deliberately left something blank; store NULL when you have no information.

Strategy

Default to NOT NULL. Ask for each nullable column: what does NULL actually mean here? If the answer is unclear, the column should probably be NOT NULL with a sensible DEFAULT. Reserve NULL for genuinely optional relationships — like an order's coupon code — where the absence of a value is meaningful.