Lesson 2 of 6 in this module

6/33 in track

NULL handling

sql10 min

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.

AnalystI can see the empty cells. Why does the query find none?
YouNULL means unknown, and unknown is never equal to anything, not even another unknown.

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.

Two sensor readings are missing
device_idmetricreadingdev-1temp21.4dev-2tempNULLdev-3temp19.1dev-4temp23.5dev-5tempNULL

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.

  1. Sensor sends a reading
  2. A packet drops or the battery dies
  1. 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.

SQL5 rows, two of them with no reading
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.

Three boxes side by side: the number 0, an empty text value, and NULL drawn in red with a dashed border, each with a caption explaining that zero is a measured value, empty text is a blank text value, and NULL means nothing was recorded.
Zero and empty text are real values. NULL is the absence of one.

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.

SQLKeep only recorded readings
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.

WordWhat it means
NULLA cell with no value. It is not zero and not blank text
IS NULLThe test for a missing value
IS NOT NULLThe test for a value that is present
SensorA device that records readings, such as temperature
Missing dataA value that was never recorded
COALESCEPick 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.

SourceWhy a cell is NULL
Customer formOptional phone number left blank
CheckoutNo promo code applied
Payment gatewayWebhook has not arrived yet
SensorDropout, 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.

TeamNeedWhat SQL must do
MaintenanceFind sensors that sent nothingSelect the rows where reading is missing
ComplianceTrue average temperature of active shelvesAverage only the recorded readings
DashboardsShow a sensible value, not a raw NULLSubstitute 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.

SQLForcing zeros into the average
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.

SQLThe equality trap
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.

ExpressionResultWhy
21.4 < 22TRUEBoth values are known
23.5 < 22FALSEBoth values are known
NULL < 22UNKNOWNThe left side is unknown
NULL = NULLUNKNOWNTwo 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.

SQLEquality versus IS NULL
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.

SQLFiltering correctly with IS NULL
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_idreadingreading < 22Row kept?
dev-121.4TRUEYes
dev-2NULLUNKNOWNNo
dev-319.1TRUEYes
dev-423.5FALSENo
dev-5NULLUNKNOWNNo

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.

SQLReadings under 22
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.

SQLInclude missing readings on purpose
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.

Watch outComparisons with NULL silently drop rows

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.

SQLThree views of 5 rows
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.

SQLShow a fallback for display
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.

SQLSkipping blanks versus substituting zeros
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.

Watch outReplacing NULL is a business decision, not a formatting trick

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.

SQLA text fallback for a number
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.

SQLCast first, then fall back
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.

SQLData quality check on 480 rows
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.

SQLThe same trap on 480 rows
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?

One missing reading, start to finish
  1. dev-2 arrives with no reading. The row is stored, the cell is NULL.

  2. reading = NULL is UNKNOWN, so it finds nothing. reading IS NULL finds dev-2 and dev-5.

  3. reading < 22 is UNKNOWN for dev-2, so WHERE drops it silently. OR reading IS NULL brings it back.

  4. COUNT(*) is 5 and COUNT(reading) is 3. AVG skips the blanks and gives 21.33.

  5. COALESCE can show 0 for display, but treating blanks as zero in the average gives 12.8.

Keep these.

  1. NULL means unknown. It is not 0 and not empty text.
  2. Find missing cells with IS NULL, never = NULL.
  3. WHERE keeps only TRUE. UNKNOWN rows drop silently, so add OR col IS NULL when you need them.
  4. Aggregates skip NULL. COUNT(*) minus COUNT(col) is the blank count.
  5. 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.

TableValues
usersu1, u2, u3
bannedu2, NULL
SQLNOT IN versus NOT EXISTS
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_id
user_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.

SQLNULLIF and a safe ratio
SELECT sku, units_sold, units_returned,
       units_returned * 1.0 / NULLIF(units_sold, 0) AS return_rate
FROM stock ORDER BY sku
sku  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.

Rate:
Was this useful?