SQL NULL explained: IS NULL and COALESCE

NULL, the value that isn't there

Sometimes a value is simply unknown. Homework that has not been marked yet has no mark. It is not zero (that would be harsh), it is missing. SQL writes "missing" as NULL.

NULL is weird, and the weirdness is on purpose. Any comparison with NULL gives "unknown", and WHERE only keeps rows where the answer is a clear yes. So this returns nothing, every single time:

SELECT task FROM homework WHERE mark = NULL;

No error, no warning, just an empty result and you wondering what you did wrong. Think of it this way: "is this unknown mark equal to some other unknown thing?" Nobody can say yes to that, so the row is dropped. Even NULL = NULL is not true.

To ask "is this missing?", SQL has a special operator:

SELECT task FROM homework WHERE mark IS NULL;
SELECT task FROM homework WHERE mark IS NOT NULL;

NULL also sneaks into maths. mark + 5 is NULL when mark is NULL, because "unknown plus five" is still unknown. And WHERE mark < 50 will skip unmarked rows too, which might be what you want, or might quietly hide them.

When you would rather show a fallback, use COALESCE. It returns the first value in its list that is not NULL:

SELECT task, COALESCE(mark, 0) AS mark_or_zero FROM homework;

Note that the empty text '' and the number 0 are not NULL. They are real values that happen to be empty or zero. NULL means "no value at all".

Tables in this lesson

homework 7 rows

id	task	subject	due	mark
1	Fractions sheet	Maths	2026-10-01	18
2	Volcano poster	Geography	2026-10-02	NULL
3	Poem about autumn	English	2026-10-03	12
4	Lab write-up	Science	2026-10-05	NULL
5	French vocab test	French	2026-10-06	0
6	Essay on Romans	History	2026-10-08	15
7	Algebra quiz	Maths	2026-10-09	NULL

Show the SQL that built them

CREATE TABLE homework (id INTEGER PRIMARY KEY, task TEXT, subject TEXT, due TEXT, mark INTEGER);
INSERT INTO homework VALUES
 (1, 'Fractions sheet', 'Maths', '2026-10-01', 18),
 (2, 'Volcano poster', 'Geography', '2026-10-02', NULL),
 (3, 'Poem about autumn', 'English', '2026-10-03', 12),
 (4, 'Lab write-up', 'Science', '2026-10-05', NULL),
 (5, 'French vocab test', 'French', '2026-10-06', 0),
 (6, 'Essay on Romans', 'History', '2026-10-08', 15),
 (7, 'Algebra quiz', 'Maths', '2026-10-09', NULL);

Try it yourself

Edit it. Break it. Run it again.

-- Try changing IS NULL to = NULL and run it again
SELECT task, subject, mark
FROM homework
WHERE mark IS NULL;

Your turn

Type it yourself. That is the whole trick.

1Already marked

Return the task and mark of every piece of homework that has been marked (mark is not missing). The French test with a mark of 0 counts as marked.

SELECT task, mark
FROM homework
WHERE mark <> NULL;

2Fill the gaps

Return every task together with its mark, but show 0 instead of NULL for unmarked work. Call the second column mark_or_zero.

SELECT task, mark AS mark_or_zero
FROM homework;