Lesson 5 of 20

In progress

Course progress

SQL

NULL and types

Why NULL is not zero or an empty string, how COALESCE rescues reports, and what SQLite's flexible typing lets you get away with.

NULL is the single most misunderstood value in SQL. It is not zero, not an empty string, and not false. It means "unknown", and it propagates through expressions in ways that quietly change answers.

What you will learn

  • Predict how NULL behaves in comparisons, arithmetic and aggregates.
  • Use COALESCE, NULLIF and IS NULL deliberately.
  • Understand type affinity and why SQLite accepts almost anything.

Three-valued logic

SQL conditions have three outcomes: true, false and unknown. WHERE keeps only true.

SQL
Editable. Runs in your browser.





Press Run to see the output.

Every comparison involving NULL yields unknown:

NULL = NULL      -- unknown
NULL <> 'x'      -- unknown
NULL > 5         -- unknown
NULL AND true    -- unknown
NULL OR true     -- true

That last line surprises people: unknown OR true is true because the true side settles it. unknown AND false is false for the same reason.

Arithmetic and concatenation

SQL
Editable. Runs in your browser.





Press Run to see the output.

LENGTH(NULL) is NULL, not 0. NULL || ' ext' is NULL, not ' ext'. Concatenation with an unknown piece produces an unknown whole — which is logically right and visually confusing when a report shows blanks.

1 + NULL is NULL. Any arithmetic that touches a null becomes null, so a total computed from a nullable column can vanish. SUM skips nulls, which is usually what you want, but it means a sum of all-null values is NULL rather than 0.

COALESCE and NULLIF

SQL
Editable. Runs in your browser.





Press Run to see the output.

COALESCE returns the first non-null argument. It is the standard tool for defaults in output, and it can chain as many values as you like.

NULLIF(a, b) returns NULL when a = b, otherwise a. Its classic use is preventing division by zero:

SQL
Editable. Runs in your browser.





Press Run to see the output.

Division by zero would raise an error in most databases; dividing by NULL returns NULL, which a report can display as unknown.

COUNT and the NULL distinction

SQL
Editable. Runs in your browser.





Press Run to see the output.

COUNT(*) counts rows. COUNT(email) counts rows where email is not null. The gap is the number of missing values, which is often the interesting number.

AVG(score) divides by the count of non-null scores, not by the row count. If three of ten rows have no score, the average is over seven. That is correct for "average of known scores" and wrong for "average including zeros", which needs AVG(COALESCE(score, 0)).

Type affinity in SQLite

Most databases enforce column types strictly: inserting 'abc' into an INTEGER column fails, or is cast. SQLite uses type affinity, which is a preference rather than a rule.

SQL
Editable. Runs in your browser.





Press Run to see the output.

A column declared without a type accepts anything, and typeof() reports the storage class of each value. That flexibility is convenient for experiments and dangerous for data integrity: a price column can end up holding text.

The practical rule: always declare types, add constraints, and do not rely on SQLite to reject a wrong value for you.

Booleans, dates and money

SQLite has no boolean type. Use INTEGER with CHECK (flag IN (0, 1)), and read it as true or false in the application.

There is no date type either. Store ISO 8601 text (2025-02-14) or a Unix timestamp integer. ISO text sorts correctly as text, which makes comparisons and ORDER BY work without conversion.

Money should be an integer in the smallest unit — paise, cents — never a float. 0.1 + 0.2 is not 0.3 in binary floating point, and a ledger that drifts by a paisa per transaction is a support ticket.

Writing WHERE email <> 'x' and assuming NULL rows are included. They are not, because the comparison is unknown. Add OR email IS NULL when missing values belong in the result.

Report each contact with a display name, a phone number defaulting to 'not provided', and a flag that is 'complete' only when both email and phone exist. Count how many contacts are complete.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

COALESCE supplies readable defaults without changing stored data. The CASE builds a label, and the count uses the same predicate so the number matches the rows labelled complete.

Recap

  • NULL is unknown: it propagates through arithmetic and comparisons, and WHERE drops those rows.
  • COALESCE supplies defaults; NULLIF turns a dangerous value into NULL.
  • Declare types and constraints even in SQLite, whose affinity will not protect you.

Module 2 begins with DISTINCT and aggregates, where NULL changes the arithmetic again.

Should I store empty strings or NULL?
Use NULL for missing data and the empty string for a real, deliberate empty value. Mixing them makes “how many are missing” unanswerable.
Is NULL equal to itself in a UNIQUE column?
Most databases allow multiple NULLs in a unique column, because each NULL is unknown and cannot conflict.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.