nulls

NULL represents an unknown or missing value in many database systems. It is not equal to zero, an empty string, or false, and it can apply to any data type. NULLs allow databases to express incomplete data without implying a specific value.

In SQL, NULL participates in three-valued logic: true, false, or unknown. Comparisons using NULL with standard operators yield unknown, so expressions like col = NULL do not evaluate to true. To test presence or absence of a value, SQL uses IS NULL and IS NOT NULL. To replace NULLs in expressions, functions such as COALESCE (or database-specific equivalents like NVL or IFNULL) return the first non-null argument.

NULLs affect aggregates and grouping. COUNT(column) counts only non-null values, while COUNT(*) counts all rows. SUM and AVG skip NULLs; MIN and MAX ignore NULLs. When grouping, NULLs are treated as a single group for grouping purposes.

Data modeling often uses NOT NULL to require a value. A column can be defined as nullable or not; foreign key columns may permit NULLs to indicate unknown or inapplicable relationships. DEFAULT values can fill NULLs during inserts.

Practical considerations include index design and query performance involving NULLs, and the fact that NULL handling can vary across database engines for certain features such as sorting NULLs (NULLS FIRST/LAST) or window functions. Understanding NULL semantics is essential for correct data validation, querying, and reporting.