Lesson 5 of 20
In progress
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,NULLIFandIS NULLdeliberately. - 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.
Editable. Runs in your browser.
INSERT INTO contacts (id, name, email, phone) VALUES
(1, 'Ana', 'ana@example.com', '9000000001'),
(2, 'Ravi', NULL, '9000000002'),
(3, 'Meera', 'meera@example.com', NULL),
(4, 'Sam', NULL, NULL);
SELECT id, name FROM contacts WHERE email IS NULL;
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
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
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:
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
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.
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
Editable. Runs in your browser.
SELECT COUNT(*) AS complete
FROM contacts
WHERE email IS NOT NULL AND phone IS NOT NULL;
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
NULLis unknown: it propagates through arithmetic and comparisons, andWHEREdrops those rows.COALESCEsupplies defaults;NULLIFturns 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?
Is NULL equal to itself in a UNIQUE column?
Progress is saved in this browser only. No account, no email.