Understanding NULL

The special "we don't know" value that lives in databases

Big Idea: NULL is not zero and not empty text. It means "there is no value here" - the information is missing or unknown. Because of that, NULL behaves in some surprising ways, and this topic shows you how to handle it safely.

By the end you will be able to:

say what NULL really means (and what it does not mean),
find missing values with IS NULL and IS NOT NULL,
explain why = NULL never works,
predict what happens when NULL meets comparisons and arithmetic,
replace missing values with a friendly default using COALESCE.

1. Our example table

All through this lesson we use one tiny school table called students. Some cells are on purpose left empty - those empty cells hold NULL.

CREATE TABLE students (
    id     INT PRIMARY KEY,
    name   VARCHAR(30),
    age    INT,
    club   VARCHAR(20),
    email  VARCHAR(40)
);

INSERT INTO students (id, name, age, club, email) VALUES
(1, 'Ava',   12,   'Chess', 'ava@school.edu'),
(2, 'Ben',   13,   NULL,    'ben@school.edu'),
(3, 'Cara',  NULL, 'Art',   NULL),
(4, 'Dylan', 12,   NULL,    NULL),
(5, 'Elly',  14,   'Chess', 'elly@school.edu');

Here is the whole table. The grey NULL badges show where a value is missing:

id	name	age	club	email
1	Ava	12	Chess	ava@school.edu
2	Ben	13	NULL	ben@school.edu
3	Cara	NULL	Art	NULL
4	Dylan	12	NULL	NULL
5	Elly	14	Chess	elly@school.edu

Ben and Dylan have not joined a club. Cara did not tell us her age. Cara and Dylan have no email on file. All of those blanks are NULL.

2. What does NULL mean?

Think of a paper form. One question asks "Which club did you join?" and a student leaves it blank. You do not know the answer - maybe they joined a club and forgot to write it, maybe they joined none. The box is simply empty. In a database that empty box is NULL.

NULL means "missing" or "unknown". It is the database way of saying "I have no value here."

Analogy: A price tag that fell off a shirt does not mean the shirt is free (0). It means you do not know the price yet. NULL is the missing price tag, not a price of zero.

Understanding NULL