Lesson 9 of 17 · Summarising Data
Working With NULL
Understand NULL in SQL. Learn why = NULL never works, how to use IS NULL and IS NOT NULL, how aggregates treat missing values and how COALESCE fills them in.
- 9 min read
- Beginner
- Reviewed
- What NULL means, and what it does not mean
- Why comparing with = NULL never works
- How to find missing values with IS NULL
- How to replace NULL with a default using COALESCE
The value that means "unknown"
Real data has gaps. A customer did not give a phone number. A student has not finished a course, so there is no score. A sensor failed and recorded nothing.
SQL marks a missing value with NULL. It does not mean zero, and it does not mean empty text. It means "there is no value here".
You have already seen it in the practice database.
SELECT id, student_id, course_id, progress, score FROM enrollments WHERE id <= 4;
The student in the second row is 60% of the way through a course. A score of 0 would be wrong: they have not failed, they simply have no score yet.
Why = NULL does not work
Try to find the enrollments without a score.
SELECT id, student_id, score FROM enrollments WHERE score = NULL;
No rows, even though you know some scores are missing. This is the best-known trap in SQL.
NULL means "unknown". Is an unknown value equal to another unknown value? The honest answer is "unknown", and WHERE only keeps rows where the answer is definitely true. So a comparison with NULL never matches anything, not even another NULL.
IS NULL and IS NOT NULL
SQL has special operators for this job.
SELECT id, student_id, course_id, progress FROM enrollments WHERE score IS NULL;
SELECT id, student_id, score FROM enrollments WHERE score IS NOT NULL ORDER BY score DESC LIMIT 5;
NULL spreads through calculations
Any arithmetic with NULL gives NULL. Unknown plus five is still unknown.
SELECT id, score, score + 5 AS with_bonus FROM enrollments WHERE id <= 4;
The same happens when joining text. You saw this earlier, when one student's sentence came out as NULL because the country was missing.
A hidden trap: NOT and NULL
A missing value fails both a condition and its opposite. How many students are from India, and how many are not?
SELECT COUNT(*) AS from_india FROM students WHERE country = 'India'; SELECT COUNT(*) AS not_from_india FROM students WHERE country <> 'India'; SELECT COUNT(*) AS everyone FROM students;
Two plus nine is eleven, but there are twelve students. The student with no country appears in neither group, because the database cannot say whether an unknown country is India or not. When a column can be NULL, decide what should happen to those rows and say so:
SELECT COUNT(*) AS not_india_or_unknown FROM students WHERE country <> 'India' OR country IS NULL;
Aggregates skip NULL
As you saw earlier, COUNT(column), SUM, AVG, MIN and MAX ignore missing values. Only COUNT(*) counts every row.
SELECT COUNT(*) AS rows_in_table,
COUNT(score) AS scores_present,
COUNT(*) - COUNT(score) AS scores_missing
FROM enrollments;Subtracting one count from the other is a handy way to measure how much data is missing.
COALESCE: a default for missing values
COALESCE returns the first of its values that is not NULL. Use it to show something friendlier, or to supply a default for a calculation.
SELECT name, COALESCE(country, 'Unknown') AS country FROM students WHERE id >= 10;
SELECT id, score, COALESCE(score, 0) AS score_or_zero FROM enrollments WHERE id <= 4;
Where does NULL sort?
Database systems disagree. In this practice database, NULL comes first when sorting in ascending order. In PostgreSQL it comes last. If it matters, test it, or filter the missing values out.
Common mistakes
Find the students who have no country on record. Show their name.
-- Show the name of every student whose country is missing
How many rows does SELECT * FROM enrollments WHERE score = NULL; return?
Summary
NULLmeans a value is missing or unknown. It is not zero and not empty text.= NULLnever matches. UseIS NULLandIS NOT NULL.- Calculations with
NULLgiveNULL. - Aggregates skip
NULL, exceptCOUNT(*). COALESCE(column, default)replacesNULLwith a value you choose.
You have finished the module on summarising data. Next, you will learn how tables are connected.
Finished reading? Tick it off to track your progress.