What does a NULL in a relational database actually represent, and why is it usually described as a marker rather than as a value?
answer
- NULL = absent information, not zero/''/false
- marker, not a domain value
- unknown vs inapplicable share one marker
- comparisons yield UNKNOWN, hence 3VL
- null bitmap in the row header
basics
~20 sNULL means information is absent - the value is unknown or does not apply. It is not zero, an empty string, or false. It is a marker saying 'no value here' rather than a member of the column's domain, which is why comparisons involving it answer UNKNOWN instead of true or false.
solid answer
~50 sNULL records the *absence* of information. Two very different absences share the marker: the value exists but we do not know it (a birth date never collected), and no value can meaningfully exist (a shipping date for a cancelled order). It is called a marker rather than a value because it is not a member of the column's domain. There is no 'the NULL integer'. Physically an engine typically records it as a bit in a per-row null bitmap and stores no data for the column at all - which is why mostly-empty nullable columns are cheap. Logically, since there is nothing to compare, any comparison involving it yields neither true nor false but a third outcome, UNKNOWN. That third outcome is the whole reason SQL uses three-valued logic instead of boolean logic. The consequences reach everywhere: constraint checking, uniqueness, indexing and aggregation each define their own rule for absent information, and those rules are not the same.
go deeper
Define it crisply: NULL means no value recorded - unknown or not applicable - and it is not zero, empty string or false.
Add why a third truth value UNKNOWN exists, and that different subsystems treat UNKNOWN differently.
Distinguish unknown from inapplicable, and note that storage, indexing, uniqueness and constraints each have their own rule for absence.
Argue the schema policy: NOT NULL by default, nullable only where absence is part of the domain and its meaning is documented, never sentinels.
## Absence, not a value In the relational model every attribute of a tuple draws its value from a domain - a type such as integer or date. NULL is not one of those values. It is a marker attached to a position in a row meaning 'this attribute has no value here'. That is more than pedantry: if NULL were a value it would have to compare equal to itself, sort in a defined place, and behave like other members of its type. It does none of those consistently, precisely because it stands in for missing information. A useful mental test: NULL is not the number zero (a known quantity), not the empty string (a known string of length zero), and not false (a known truth value). A zero balance means we know the balance and it is nothing; a NULL balance means we do not know it. Conflating them is the single most common source of wrong results in real systems, because zero participates in sums and averages while absence does not. ## Two kinds of absence under one marker SQL offers exactly one marker for at least two situations. **Missing but applicable**: the fact exists in the world and has not been recorded - a phone number the customer did not give. **Missing and inapplicable**: no fact can exist - a termination date for a currently employed person, a maiden name for someone who never married. Storing both as NULL means a query can never distinguish 'we do not know' from 'there is nothing to know', and every answer inherits that ambiguity. This is the ambiguity Codd himself later attacked. ## Why a third truth value follows Once a marker for 'no information' exists, a predicate applied to it cannot honestly return true or false. Asking whether an unknown salary exceeds 50000 has no determinate answer: it might, it might not. SQL's answer is a third truth value, UNKNOWN, and it propagates through conditions built from it. Different parts of the language then decide separately what to do with UNKNOWN - filtering discards it, integrity constraints accept it - and those different decisions are where most surprises live. ## How engines store it Most row-store engines keep a null bitmap in the row header: one bit per nullable column indicating presence, with no bytes stored for the absent value. This makes a wide table of mostly-absent columns compact, and it is why adding a nullable column is generally cheap in engines that do not have to rewrite every row. It also explains why NULL has no type of its own at rest - the column's declared type still governs; the bitmap merely says the slot is empty. Indexes vary in whether they store entries for rows with absent keys at all, which affects whether a query looking for absent values can use an index. That detail is engine-specific, but the concept to carry is that 'absent' is a special case at every layer, not a normal value flowing through. ## Practical stance Because one marker covers several meanings and every subsystem treats it slightly differently, the mature approach is to decide deliberately, per column, whether absence is possible and what it would mean. Columns representing facts that always exist should be NOT NULL so the ambiguity never arises. Columns where absence is genuinely part of the domain should be nullable and documented: which of the two absences does a NULL here represent? And nobody should invent sentinel values such as 0, the empty string, or a date of 1900-01-01 to dodge the issue, because a sentinel is a real value that quietly joins aggregates, comparisons and unique keys as though it were data.
- How is a NULL different from an empty string or a zero?An empty string and a zero are known values in their domain: you recorded them, they compare equal to themselves, and they participate in sums, lengths and unique keys. NULL records that no value was captured, so it has no length, no magnitude, and comparisons against it are unknown. Using zero as a stand-in for 'unknown' corrupts averages and totals.
- Why do people say a NULL literal has no data type?The absence marker itself carries no type - only the column's declaration does. That is why a bare NULL literal in an expression often has to be cast for the engine to know which type rules apply. At rest the column's declared type still governs; the null bitmap only records that the slot is empty.
A blank on a paper form. It may mean 'I do not know my blood type' or 'this question does not apply to me' - but either way the blank is not an answer you can do arithmetic with.
saying these in an interview costs you the question
- Saying NULL equals zero, the empty string, or false
- Claiming two NULLs are equal to each other
- Treating NULL as a normal value of the column's type
- Using a sentinel like 0 or 1900-01-01 to represent unknown and calling it cleaner