◌NULL values
Unknown, not “empty” or zero
NULL means the value is unknown or not applicable. It is not the number zero, not an empty string, and not “false.” Comparisons and ordinary arithmetic with NULL yield UNKNOWN in three-valued logic; WHERE keeps only rows where the predicate is TRUE, so UNKNOWN rows disappear, often the first place junior queries “lose” rows silently.
WHERE col = NULL
= NULL is never TRUE in standard SQL, it is UNKNOWN, so the filter matches nothing. This typo survives code review because it looks plausible.
WHERE col IS NULL
Use IS NULL / IS NOT NULL. For “equals or both missing,” spell out (a = b OR (a IS NULL AND b IS NULL)) or use a helper view.
IS NULL / IS NOT NULL
SELECT sample_id, concentration_ng_ul
FROM extractions
WHERE concentration_ng_ul IS NULL;| sample_id | concentration_ng_ul |
|---|---|
| S-440 | NULL |
| S-441 | NULL |
WHERE keeps only TRUE: equality with NULL is UNKNOWN
SELECT a, b, (a = b) AS eq_unknown
FROM (VALUES (1, NULL), (2, 2)) AS t(a, b)
WHERE a = b;| a | b | eq_unknown |
|---|---|---|
| 2 | 2 | true |
📐How UNKNOWN shows up in everyday predicates
If a column can be NULL, every join and filter on that column participates in three-valued logic.
| Expression | If a operand is NULL |
|---|---|
| a = NULL | UNKNOWN: never treated as TRUE in WHERE |
| a IS NULL | Deterministically TRUE or FALSE |
| a IN (v1, v2, NULL) | TRUE only if a equals a listed non-NULL value; otherwise often UNKNOWN |
| a NOT IN (subquery) | If the subquery can yield NULL members, many engines make the predicate UNKNOWN for most rows |
📊Aggregates and NULL
COUNT(*) counts rows; COUNT(expr) skips rows where expr is NULL.
When summarizing assay plates or sequencing runs, decide whether “missing measurement” should lower the denominator. Mixing COUNT(*) and COUNT(result_value) in the same dashboard without labeling the difference has caused more than one incorrect pass-rate email.
QC and regulated data
Prefer explicit nullable columns and documented imputation rules over ad hoc NULL coercions in reporting queries, auditors ask what “missing” meant on the day the row was written.
TL;DR
NULL means “unknown / not applicable”: not zero or empty string. Comparisons with NULL yield UNKNOWN, which WHERE treats like false; use IS NULL / IS NOT NULL. Aggregates skip NULL inputs; outer joins manufacture NULLs for missing matches. Design schemas so “missing” has a documented business meaning.