Overview
NULL means unknown, not zero and not an empty string. Filter it with IS NULL, replace it with COALESCE.
On this page7 sections
The table we keep using
The middle row has no reading. SUM skips it. COALESCE can fill it if the business agrees.
NULL means a value is missing or unknown. It is not the number 0. It is not an empty string. It is not false. It is a special marker the database uses when nothing was recorded.
Beginners often write WHERE reading = NULL and get zero rows back. SQL does not treat NULL as equal to anything, including another NULL. The correct tests are IS NULL and IS NOT NULL.
COALESCE(column, fallback) returns the first non-NULL argument. It is how you fill a blank for a report. Filling is a business choice, not a default you should apply blindly.
What we need from it
A food delivery company stores driver GPS pings in a telemetry table. Some pings fail to record a reading. If you treat those blanks as zero, average speed looks slower than it is. Dispatch might add extra drivers to a zone that is actually fine.
If you ignore NULL and only filter with =, those rows vanish from WHERE clauses. Revenue that used a NULL status never shows up in a paid report, and nobody notices until finance asks why GMV dropped.
Data engineers check NULL rates the way they check uniqueness. A sudden spike in missing readings is a pipeline bug, not a quiet day.
Trace it step by step
Three rules cover most NULL work:
- Test with IS NULL / IS NOT NULL. Never use = NULL.
- COUNT(*) counts rows. COUNT(column) skips NULL in that column. SUM and AVG also skip NULL.
- COALESCE(reading, 0) fills a blank. Use 0 only when missing truly means zero activity.
NULL handling is a policy. Write the policy in the query so reviewers can see it.
| Expression | What it does | When to use it |
|---|---|---|
| col IS NULL | Keeps rows with a missing value | Find broken sensor rows |
| col IS NOT NULL | Drops missing values | Metrics that need a real number |
| COALESCE(col, 0) | Replaces NULL with 0 | Only if zero is the agreed fallback |
| NULLIF(col, '') | Turns empty string into NULL | Clean sentinel blanks |
Input: three telemetry rows. One reading is NULL.
| device_id | metric | reading |
|---|---|---|
| dev-1 | temp | 21.4 |
| dev-2 | temp | NULL |
| dev-3 | temp | 19.1 |
Same table, next cut
Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.
SELECT device_id, metric, reading
FROM telemetry
WHERE reading IS NULL;Output: only the row for dev-2. The other two rows have a number, so they fail IS NULL.
SELECT AVG(reading) AS avg_skip_null,
AVG(COALESCE(reading, 0)) AS avg_treat_as_zero
FROM telemetry;The first average uses two numbers (21.4 and 19.1). The second pretends the missing row was 0, which pulls the average down. Those two numbers answer different questions.
WHERE col = NULL never matches
SQL evaluates col = NULL as unknown, not true. Use IS NULL. The same trap exists in JOIN conditions.
Python connection
In pandas, missing values are NaN and you test them with isna(). In SQL you test with IS NULL. Same idea, different spelling.
Common beginner questions
Why can I not write WHERE reading = NULL?
Because NULL means unknown. Unknown equal to unknown is still unknown, so the row is dropped. IS NULL is the special test for 'this is the missing marker'.
Does SUM count NULL as zero?
No. SUM skips NULL. If every value is NULL, SUM returns NULL, not 0. Wrap with COALESCE(SUM(col), 0) if a report must show zero for an empty set.
Should I always fill NULL with 0?
No. A missing price is not a free product. Fill only when the business agrees on the fallback.
What comes next
The next lesson is GROUP BY. Aggregates skip NULL by default, so the IS NULL habits you just learned change how counts and averages come out.
Practice
Run Sample to see COALESCE next to a raw NULL. Then complete Exercise: from telemetry, return device_id, metric, and reading where reading IS NULL, limited to 20 rows.
Practicals · load into the editor
After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.
Practice this
Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.
- Coalesce null salaries to zeroInterview-style drill: Return each employee name with salary defaulted to 0 when NULL.Studiobeginnersql8 min
- Employees without a managerInterview-style drill: Find employees whose manager_id is NULL.Studiobeginnersql8 min
- Filter Engineering salariesInterview-style drill: Filter employees in Engineering earning more than 80000.Studiobeginnersql8 min