Overview
NULL means unknown, not zero and not an empty string. Filter it with IS NULL, replace it with COALESCE.
On this page8 sections
The table we keep using
A sensor table has a blank reading, and the analyst writes WHERE reading = NULL. The query returns nothing, and she concludes there are no blanks.
What does the 5-row sensor table look like?
This 5-row table stays on the page for the whole lesson. Watch the two highlighted rows.
The highlighted cells hold no recorded measurement. They are blank, not zero.
Rows 2 and 5 are unrecorded measurements.
Where do blank readings come from?
A cold-storage warehouse in Pune keeps medicines in refrigerated units. Sensors on the shelves send a temperature every few minutes.
- Sensor sends a reading
- A packet drops or the battery dies
- Row saved with time and device, reading blank
The database still stores the row. Only the reading cell is empty.
Can you build this table yourself?
Yes, in the editor. The real telemetry table has 480 rows in the same shape, so this small one lets you trace by hand.
CREATE OR REPLACE TABLE telemetry_demo (
device_id VARCHAR,
metric VARCHAR,
reading DOUBLE
);
INSERT INTO telemetry_demo VALUES
('dev-1', 'temp', 21.4),
('dev-2', 'temp', NULL),
('dev-3', 'temp', 19.1),
('dev-4', 'temp', 23.5),
('dev-5', 'temp', NULL);NULL in the INSERT is how you store a missing value.
How is NULL different from zero?
A blank cell has its own name in SQL: NULL. It means missing, unknown or never recorded.
A reading of 0.0 degrees means the sensor measured freezing. An empty string is valid text of length zero. NULL answers 'what was the temperature?' with 'we do not know'.
NULL does not mean zero or empty. It means we do not know.
How many rows survive IS NOT NULL?
Predict the count and the devices.
SELECT device_id, metric, reading
FROM telemetry_demo
WHERE reading IS NOT NULL;device_id metric reading dev-1 temp 21.4 dev-3 temp 19.1 dev-4 temp 23.5
3 rows. dev-2 and dev-5 were left out because their reading is NULL.
Words you will see in this lesson
Six words. The rest of the lesson uses only these.
| Word | What it means |
|---|---|
| NULL | A cell with no value. It is not zero and not blank text |
| IS NULL | The test for a missing value |
| IS NOT NULL | The test for a value that is present |
| Sensor | A device that records readings, such as temperature |
| Missing data | A value that was never recorded |
| COALESCE | Pick the first value that is not NULL |
What we need from it
Why is missing data an everyday problem?
Upstream systems often send incomplete records. Handled badly, NULL distorts sums and averages and silently drops rows.
None of these is an error. Each is data you must handle on purpose.
| Source | Why a cell is NULL |
|---|---|
| Customer form | Optional phone number left blank |
| Checkout | No promo code applied |
| Payment gateway | Webhook has not arrived yet |
| Sensor | Dropout, as in our table |
What does the operations team need?
Three teams ask three different questions about the same blank cells.
Different needs, different NULL handling.
| Team | Need | What SQL must do |
|---|---|---|
| Maintenance | Find sensors that sent nothing | Select the rows where reading is missing |
| Compliance | True average temperature of active shelves | Average only the recorded readings |
| Dashboards | Show a sensible value, not a raw NULL | Substitute a fallback for display |
What if you treat missing as zero?
The three real readings sum to 64.0, so the true average is 21.33. Predict the average if both blanks count as 0.0.
SELECT ROUND(AVG(COALESCE(reading, 0)), 2) AS avg_distorted
FROM telemetry_demo;avg_distorted 12.8
64.0 divided by five is 12.8. That drop of more than 8.5 degrees could trigger alarms for a fault that does not exist.
The warehouse was not freezing. The data was missing.
Trace it step by step
Why does WHERE reading = NULL return nothing?
It feels natural: you write = 'cancelled' for cancelled orders. Predict how many rows come back.
SELECT device_id, metric, reading
FROM telemetry_demo
WHERE reading = NULL;device_id metric reading (0 rows)
No error and no rows, even though dev-2 and dev-5 are blank.
What is three-valued logic?
SQL conditions are TRUE, FALSE or UNKNOWN. Comparing an unknown value to anything gives UNKNOWN.
WHERE keeps a row only when the condition is TRUE. FALSE and UNKNOWN both drop it.
| Expression | Result | Why |
|---|---|---|
| 21.4 < 22 | TRUE | Both values are known |
| 23.5 < 22 | FALSE | Both values are known |
| NULL < 22 | UNKNOWN | The left side is unknown |
| NULL = NULL | UNKNOWN | Two unknowns may differ |
That is why reading = NULL is UNKNOWN for all 5 rows, and all five are dropped.
Is NULL equal to NULL?
Predict what DuckDB returns for the 2 tests side by side.
SELECT NULL = NULL AS eq_null,
NULL IS NULL AS is_null;eq_null is_null NULL true
NULL = NULL is itself NULL, meaning unknown. IS NULL gives a clean true.
How do you find the blank rows?
Use IS NULL. It inspects the cell instead of comparing values.
SELECT device_id, metric, reading
FROM telemetry_demo
WHERE reading IS NULL;device_id metric reading dev-2 temp NULL dev-5 temp NULL
Both broken sensors appear, so a technician knows where to go. IS NOT NULL (shown in the previous query) is the inverse.
Test for missing with IS NULL, never with =.
What does reading < 22 do to each row?
Trace each row through the condition before you run it.
Row-by-row evaluation under three-valued logic.
| device_id | reading | reading < 22 | Row kept? |
|---|---|---|---|
| dev-1 | 21.4 | TRUE | Yes |
| dev-2 | NULL | UNKNOWN | No |
| dev-3 | 19.1 | TRUE | Yes |
| dev-4 | 23.5 | FALSE | No |
| dev-5 | NULL | UNKNOWN | No |
dev-4 fails the test because 23.5 is not below 22. dev-2 and dev-5 are dropped because UNKNOWN is not TRUE.
What does that query actually return?
Check the table above, then predict the row count.
SELECT device_id, reading
FROM telemetry_demo
WHERE reading < 22;device_id reading dev-1 21.4 dev-3 19.1
2 rows. The blank sensors vanished without a warning.
How do you keep the missing rows too?
Say so explicitly. The database never assumes you want unknown rows.
SELECT device_id, reading
FROM telemetry_demo
WHERE reading < 22 OR reading IS NULL;device_id reading dev-1 21.4 dev-2 NULL dev-3 19.1 dev-5 NULL
4 rows. Only dev-4 (23.5) is out.
Beginners often assume that WHERE reading < 22 and WHERE reading >= 22 together cover every row in a table. In reality, both queries drop dev-2 and dev-5 because NULL comparisons evaluate to UNKNOWN. To include missing rows, you must explicitly write OR reading IS NULL.
Same table, next cut
What do COUNT(*) and COUNT(reading) say?
COUNT(column), SUM, AVG, MIN and MAX skip NULL, while COUNT(*) counts every row. Predict the three numbers.
SELECT COUNT(*) AS rows_total,
COUNT(reading) AS rows_with_reading,
SUM(reading) AS total_reading
FROM telemetry_demo;rows_total rows_with_reading total_reading 5 3 64
COUNT(*) counts rows. COUNT(reading) counts only recorded cells. The sum is 21.4 + 19.1 + 23.5.
If COUNT(*) and COUNT(column) differ, some cells are NULL.
What does COALESCE do?
COALESCE checks its arguments left to right and returns the first one that is not NULL. If all are NULL, it returns NULL.
SELECT device_id, metric, reading,
COALESCE(reading, 0.0) AS reading_or_zero
FROM telemetry_demo
WHERE reading IS NULL;device_id metric reading reading_or_zero dev-2 temp NULL 0 dev-5 temp NULL 0
reading stays NULL. reading_or_zero shows 0. COALESCE changes only the output, never the stored table.
Skip the blanks or count them as zero?
Both queries are valid SQL, but they make different claims. Predict both averages.
SELECT ROUND(AVG(reading), 2) AS avg_skip_null,
ROUND(AVG(COALESCE(reading, 0)), 2) AS avg_treat_as_zero
FROM telemetry_demo;avg_skip_null avg_treat_as_zero 21.33 12.8
The first asks for the average of sensors that reported: 64.0 over 3. The second claims two sensors read 0.0: 64.0 over 5.
A missing temperature is not 0.0 degrees, and a missing discount is not a free product. Replace NULL with a fallback only when domain experts confirm that missing data has that specific meaning. When calculating physical averages or rates, let SQL aggregates skip NULLs naturally.
Can the fallback be text like N/A?
Not directly. Predict what DuckDB does with a text fallback in a number column.
SELECT COALESCE(reading, 'N/A')
FROM telemetry_demo;Conversion Error: Could not convert string 'N/A' to DOUBLE
LINE 1: SELECT COALESCE(reading, 'N/A') FROM telemetry_demo
^All COALESCE arguments must share a type. Cast the number to text first.
SELECT device_id,
COALESCE(CAST(reading AS VARCHAR), 'N/A') AS reading_text
FROM telemetry_demo;device_id reading_text dev-1 21.4 dev-2 N/A dev-3 19.1 dev-4 23.5 dev-5 N/A
How big is the problem in the real telemetry table?
The same COUNT check works on the real table the exercise uses.
SELECT COUNT(*) AS rows_total,
COUNT(reading) AS rows_with_reading
FROM telemetry;rows_total rows_with_reading 480 455
25 readings are missing. That one query tells you before any average is trusted.
Does the equality trap hit the real table too?
Yes. Count the blanks both ways and compare.
SELECT
(SELECT COUNT(*) FROM telemetry WHERE reading = NULL) AS equals_null,
(SELECT COUNT(*) FROM telemetry WHERE reading IS NULL) AS is_null;equals_null is_null 0 25
The wrong test finds none of the 25 blank readings. In pipelines, WHERE col IS NOT NULL is the standard way to clean a landing table before numbers feed a dashboard or model.
How does one blank travel through the lesson?
dev-2 arrives with no reading. The row is stored, the cell is NULL.
reading = NULL is UNKNOWN, so it finds nothing. reading IS NULL finds dev-2 and dev-5.
reading < 22 is UNKNOWN for dev-2, so WHERE drops it silently. OR reading IS NULL brings it back.
COUNT(*) is 5 and COUNT(reading) is 3. AVG skips the blanks and gives 21.33.
COALESCE can show 0 for display, but treating blanks as zero in the average gives 12.8.
Keep these.
- NULL means unknown. It is not 0 and not empty text.
- Find missing cells with IS NULL, never = NULL.
- WHERE keeps only TRUE. UNKNOWN rows drop silently, so add OR col IS NULL when you need them.
- Aggregates skip NULL. COUNT(*) minus COUNT(col) is the blank count.
- COALESCE is a business decision. Match the type of the column.
Common beginner questions
Why doesn't SQL evaluate NULL = NULL as TRUE?
Because NULL represents missing or unknown information. If you have two sealed envelopes containing unknown amounts of cash, you cannot claim they contain the exact same amount. In SQL, comparing two unknowns yields UNKNOWN, not TRUE.
What does SUM or AVG return if every row in a table or group is NULL?
It returns NULL, not zero. If there are no non-NULL numbers to add, the sum remains unknown. If your business reporting requires zero when all records are missing, wrap the aggregate: COALESCE(SUM(reading), 0).
How does SQL NULL compare to NaN in Python and pandas?
They serve similar roles as missing data markers, but handle comparisons differently. In pandas, float columns use NaN for missing values, and you detect them using df['reading'].isna(). However, arithmetic and grouping in pandas also skip NaN by default, mirroring SQL's aggregate behavior.
What comes next
In this lesson, you observed how aggregate functions skip NULL values across an entire table. In the next lesson, you will learn how to partition data into groups using GROUP BY. When grouping data, SQL bundles all NULL values together into a single distinct group, and aggregate functions continue to skip NULLs within each group.
Practice
The practice exercise asks you to query the telemetry table and return device_id, metric, and reading for rows where reading IS NULL, limited to 20 rows. Remember what you learned about three-valued logic: standard equality operators will return an empty dataset, so apply the dedicated SQL operator designed specifically for identifying missing records.
Going further: the NOT IN trap and two safe tools
NULL means unknown, so a comparison with it is neither true nor false. That rule produces the most famous silent bug in SQL: NOT IN with a list that contains a NULL. Users u1, u2 and u3 exist, and the banned list holds u2 and a NULL. Predict who is not banned.
| Table | Values |
|---|---|
| users | u1, u2, u3 |
| banned | u2, NULL |
SELECT user_id FROM users WHERE user_id NOT IN (SELECT user_id FROM banned) ORDER BY user_id;
SELECT user_id FROM users u WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.user_id = u.user_id) ORDER BY user_iduser_id (0 rows) user_id u1 u3
NOT IN returned nothing at all. For u1 the database asks 'is u1 different from u2 and different from NULL?'. The NULL part is unknown, so the whole test is unknown, and unknown rows are dropped. NOT EXISTS asks a different question (is there a matching row?) and gives the expected answer: u1 and u3.
Two more tools. NULLIF turns a value into NULL, which is the standard way to avoid dividing by zero. IS NOT DISTINCT FROM compares 2 values and treats two NULLs as equal.
SELECT sku, units_sold, units_returned,
units_returned * 1.0 / NULLIF(units_sold, 0) AS return_rate
FROM stock ORDER BY skusku units_sold units_returned return_rate A 10 2 0.2 B 0 0 NULL C 5 5 1.0
Product B sold nothing, so its rate is NULL (unknown) instead of an error. Decide what the report should show for it. COALESCE(rate, 0) would say 0%, which claims something the data does not know.
Try a new scenario
A discount column is NULL for 'no discount recorded'. Compute the net price as price minus discount, so that a missing discount means no discount and the price is kept.
Open the answer
COALESCE(discount, 0) replaces a missing discount by 0 before the subtraction, giving sku net ; A 80 ; B 250 ; C 40 ; D 989. Without it, B would have a NULL net price. Replacing NULL with 0 is a business decision: here it is reasonable, but for a missing price it would not be.
Interview: what is NULL and how is it different from zero?
NULL means unknown or missing. Zero is a real number. Comparisons with NULL give unknown, aggregates such as SUM and AVG skip NULLs, and you test for it with IS NULL, not = NULL.
Close the lesson and explain it
Without scrolling up, explain why NOT IN returns nothing when the list contains a NULL, and what to use instead.
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.
Finished this lesson?
Mark it as read when you could explain it to someone else.
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