SQL / NULL AND THREE-VALUED LOGIC
NULL means unknown, not zero or blank
Distinguish 0, the empty string and NULL in real data, and predict when one missing value turns a whole computed column into NULL.
What you will learn
- Read NULL as 'no value stored', never as 0 or an empty string
- Detect gaps with IS NULL instead of = 0 or = '', and never by eyeballing blank cells
- Predict NULL results: arithmetic, concatenation and length all return NULL if fed NULL
- Keep unknown out of columns where it is meaningless using NOT NULL and DEFAULT
Understanding NULL means unknown, not zero or blank
A NULL in a row does not mean the number is zero or the text is blank; it means the database is storing no value in that slot at all. Zero is a measurement you can add up, and '' is a string that happens to have length zero, so both are real values occupying the column. NULL is the absence of one, which is why units_in_stock = 0 says a shelf was checked and found empty while units_in_stock IS NULL says nobody has checked it. That distinction only survives if the people loading the data respect it: once a gap is written as 0, nothing downstream can tell it from a genuine count.
Because NULL is not a value, an expression that needs to read it cannot produce one either. 4 + NULL is NULL because a sum with an unknown addend is unknown, and 'Ada ' || NULL is NULL for the same reason. The propagation is mechanical rather than algebraic: 0 * NULL is still NULL, even though multiplying by zero gives zero for every real number, because SQL never speculates about which value is missing. One absent input therefore blanks out every column computed from it, which is how a single uncounted row quietly drops a line total out of a report.
The model that keeps this straight is a per-cell flag: each value in a row is either present or absent, and NULL is the flag turned off, carrying no zero, no blank and no type-specific meaning of its own. Gaps get that flag set by INSERTs that omit a column, by outer joins that find no matching row, and by an explicit NULL in the statement. Clients then render the flag however they like, psql printing an empty cell where other tools print (null), so a blank on screen never proves what is stored and the only trustworthy test is IS NULL.
CREATE TABLE reading (
sensor text,
celsius numeric,
note text
);
INSERT INTO reading VALUES
('a1', 0, ''),
('b2', NULL, NULL);
SELECT sensor,
celsius,
celsius + 1 AS plus_one,
note IS NULL AS note_missing,
length(note) AS note_length
FROM reading;NULL marks the absence of a value, so it is neither 0 nor '' and anything computed from it is unknown too.
Worked examples
Unknown spreads into computed columns
Shows a missing quantity turning a known price into an unknown line total.
CREATE TABLE cart (item text, qty integer, price numeric);
INSERT INTO cart VALUES
('mug', 2, 6.50),
('poster', NULL, 4.00);
SELECT item,
qty,
price,
qty * price AS line_total,
(qty * price) IS NULL AS total_unknown
FROM cart;Example explained
Line 1qty is NULL for the poster because nobody counted that shelf, while the mug's 2 is a real count.
Line 2qty * price is NULL only for the poster: one absent factor makes the product unknown even though price is known.
Line 3If qty were 0 the line_total would read 0.00, so the row would still carry a number; that is the practical gap between zero and unknown.
Line 4total_unknown uses IS NULL, the only test that reports presence rather than comparing values.
Where NULLs come from: omitted columns and defaults
Shows an omitted column storing NULL, a DEFAULT storing a real zero, and an explicit NULL overriding that default.
CREATE TABLE signup (
email text NOT NULL,
referrer text,
score integer DEFAULT 0
);
INSERT INTO signup (email) VALUES ('a@example.com');
INSERT INTO signup (email, referrer, score) VALUES ('b@example.com', 'newsletter', NULL);
SELECT email, referrer, referrer IS NULL AS referrer_missing, score
FROM signup;Example explained
Line 1email is NOT NULL, so an unknown address cannot be stored at all; passing NULL there aborts the INSERT with a not-null constraint error.
Line 2The first INSERT omits referrer, so the row records no claim about where that user came from, which is not the same as storing ''.
Line 3The same INSERT omits score, so DEFAULT 0 fires and a genuine zero is stored, giving one statement both an absent value and a real zero.
Line 4The second INSERT passes NULL for score explicitly, and an explicit NULL beats the default, so the column ends up unknown rather than 0.
Important notes
Oracle is the exception to the '' versus NULL distinction: it stores an empty string in a VARCHAR2 as NULL, so those two states cannot be separated there.
NULL carries no type of its own, so inside a VALUES list or a UNION a bare NULL may need a cast such as NULL::date before the column gets the type you intended.
Common mistakes
Importing blank spreadsheet cells as 0 or '': 'never measured' silently becomes 'measured zero', and the gap cannot be recovered afterwards.
Assuming a blank cell in a GUI grid is an empty string: length, trim and upper all hand back NULL for it, and application code expecting text receives a null.
Expecting qty * price to be 0 when qty is NULL: the line total comes back NULL, so the row shows an empty cell instead of 0.00 and the bug looks like a formatting problem.
Try it yourself
Change, predict, then run
Create a table with one integer column visits, then insert three rows: one holding 0, one holding an explicit NULL, and one that omits the column entirely. Select visits, visits IS NULL and visits + 10 for all three rows and note which cells come back empty and why.
Open the SQL workspaceCheck your understanding
A products table stores units_in_stock (integer) and discontinued_on (date). A product is still on sale and nobody has ever counted its stock. Which pair of values records exactly what is known?
- units_in_stock 0 and discontinued_on NULL, since zero is a safe placeholder for a count nobody has taken
- units_in_stock 0 and discontinued_on '', so both columns stay filled and reports never see a gap
- units_in_stock NULL and discontinued_on NULL, since no count exists and no discontinue date exists
- units_in_stock -1 and discontinued_on NULL, using a sentinel that can be told apart from a real zero
Show answer
Both facts are absences, so NULL is the honest entry in each column; NULL covers 'unknown' as well as 'not applicable', but in neither case is it a value. Option 0 is tempting because a filled column keeps arithmetic simple, yet 0 asserts that someone counted and found nothing, which drops the product into every out-of-stock report. The -1 sentinel is worse than NULL because it survives arithmetic and corrupts any calculation whose author forgot the convention, and '' is not even a valid date.