How do relational databases handle NULL values?

Relational databases handle NULL values as a way to represent missing, unknown, or inapplicable data. Unlike empty strings or zero values, NULL explicitly indicates the absence of a value. For example, in a table storing employee records, a NULL in the “termination_date” column might mean the employee is still active, whereas an empty string could be ambiguous. Databases allocate storage for NULLs efficiently, often using bitmap flags to track which columns in a row contain NULLs. This avoids reserving space for default values, which keeps tables compact but requires special handling during queries.

When performing operations involving NULL values, relational databases follow specific rules. Arithmetic expressions with NULL typically return NULL (e.g., 5 + NULL results in NULL), and logical comparisons like WHERE column = NULL will not work as expected. Instead, developers must use IS NULL or IS NOT NULL to check for NULLs. Aggregate functions like SUM() or AVG() ignore NULL values by default, but this can lead to surprises. For instance, SUM(salary) where some salaries are NULL will exclude those entries, effectively treating them as zero. Developers must use functions like COALESCE() or IFNULL() to replace NULLs with defaults before calculations.

NULLs also affect database constraints and joins. A foreign key column with NULL is allowed even if no matching primary key exists, as NULL doesn’t violate referential integrity. Unique constraints treat NULLs as distinct: a unique email column can have multiple NULLs since they don’t represent actual values. However, joins involving NULLs can produce unexpected results. For example, an INNER JOIN between tables on columns with NULLs will exclude those rows, while OUTER JOIN retains them. Developers should explicitly handle NULLs in schema design—such as using NOT NULL constraints where applicable—to avoid ambiguity and ensure data consistency.