Dates and missing values

Two things that fool beginners

Two things catch almost every beginner out: values that are missing, and dates. Both behave in ways you would not guess. Both are easy once you know the rule.

Comparing with NULL finds nothing

Some readings were never taken, so their systolic value is missing. In SQL a missing value is called null. Ask for the rows where systolic equals null, and the database returns no rows at all, even though two readings are missing. The reason: an ordinary comparison with null yields null, meaning unknown, not true.

• SELECT id, patient
• FROM readings
• WHERE systolic = NULL;

Use IS NULL to find missing values

The correct test is is null. The tutorial's advice is blunt: do not write equals null, because null is not equal to null. A null value represents an unknown value, and it is not known whether two unknown values are equal. With is null, the two readings with no value are found.

• SELECT id, patient
• FROM readings
• WHERE systolic IS NULL;

Aggregates skip the missing values

Nulls also change aggregates. Count star counts every row: six. Count of systolic counts only the rows where systolic is not null: four. And the average is computed over those four values alone. SQLite's documentation says exactly this: count of x is the number of times x is not null, and the average is of all non null values.

• SELECT count(), count(systolic),
• avg(systolic)
• FROM readings;

Day-first text sorts the wrong way

Dates need care too. Here, dates are stored as text in day, month, year form, the style many people write. Sorted as text, the rows come out as the ninth of March, then the tenth of February, then the twenty fifth of January: the wrong order, because text sorts character by character. The year is the last thing compared.

• SELECT d
• FROM dates_text
• ORDER BY d;

Year-first text sorts correctly

SQLite has no separate date storage class. Its documentation says dates can be stored as text in the year, month, day, ISO eight six zero one form, as a Julian day number, or as a Unix time. Year first text sorts correctly, because the biggest unit comes first. And to count days between two dates, subtract their Julian day numbers, as the documentation shows.

• SELECT d,
• julianday(d) -
• julianday('2026-01-25') AS days
• FROM dates_iso
• ORDER BY d;

Check yourself

Pause and think. How do you find the rows where a column has no value? The answer: where column is null.

• WHERE column IS NULL
• WHERE column = NULL
• WHERE column = 0
• WHERE column = ''

Keep these in mind

Null means unknown, so equals null is never true. Use is null to find missing values. Count of x and average skip nulls, while count star does not. And store dates year first, so they sort in time order.

• NULL means unknown; = NULL is never true
• Use IS NULL to find missing values
• count(x) and avg(x) skip nulls; count() does not
• Store dates year first so they sort in time order