In SQL, NULL signifies the absence of data, indicating that a value is missing, unknown, or inapplicable.
Because NULL denotes an unknown state, SQL operates under three-valued logic rather than traditional two-valued boolean logic.
In three-valued logic, logical expressions evaluate to one of three states: true, false, or unknown.
Any standard arithmetic operation involving NULL yields NULL, because adding five to an unknown value remains unknown.
Similarly, comparing any value to NULL using equals or inequality yields unknown rather than true or false.
Even testing whether NULL equals NULL evaluates to unknown, because two unknown values cannot be presumed identical.
Because WHERE clauses accept only rows evaluating strictly to true, comparisons with equals NULL silently discard all rows.
The dedicated operators IS NULL and IS NOT NULL test for the physical presence or absence of data reliably.
The COALESCE function takes multiple expressions and returns the first non-null entry, useful for replacing missing fields.
The NULLIF function compares two expressions and returns NULL if they are equal, commonly used to prevent division-by-zero errors.
When performing joins, NULL values on join keys do not match each other, dropping those rows in standard inner joins.
In unique constraints, standard SQL permits multiple rows with NULL values, treating each missing entry as distinct.
In sorting operations, SQL implementations differ on whether NULLs are treated as smaller or larger than all numbers.
Aggregate functions like SUM, AVG, and MIN skip NULL values automatically, which can skew expected sample totals.
Analysts must actively handle NULL values during data cleaning, distinguishing between genuine zeroes and unrecorded facts.