Null Values

Definition

A NULL value represents a missing, unknown, inapplicable, or not-yet-recorded value for an attribute in a tuple. Formally, NULL is not a member of any domain — it is a special marker denoting the absence of a value, distinct from zero, an empty string, or any other "real" value.

Example

If Karan hasn't declared a department yet, his tuple might be (4, Karan, NULL, 6.8). If a newly enrolled student has no GPA yet (no exams taken), (5, Priya, CS, NULL) is a valid tuple — the gpa attribute simply has no value recorded.

How NULL breaks relation purity

Strict relational theory defines a relation as a SET of tuples, meaning duplicates are impossible and tuple equality is always well-defined. NULL undermines this cleanliness:

• Two tuples that both contain NULL in the same attribute position cannot be reliably compared for equality. Under three-valued logic, NULL = NULL evaluates to UNKNOWN, not TRUE. This means the very notion of "duplicate tuple" — which underpins the definition of a relation as a set — becomes ambiguous whenever NULLs are involved. Two "identical-looking" tuples that each carry NULL in different (or the same) attribute positions are still treated as logically distinct rows by most systems, precisely because equality can't be established.
• This is why relational purists (e.g., C. J. Date) criticize NULL as inconsistent with the clean set-theoretic foundation of the relational model — even though virtually every real-world DBMS supports it.

Kinds of NULL (conceptually)

• Missing but applicable: the value exists in reality but just hasn't been recorded yet (e.g., gpa before the first exam).
• Missing and inapplicable: the value doesn't make sense for this tuple (e.g., a "spouse_name" attribute for an unmarried person).

Most DBMSs do not distinguish these kinds internally — both are stored using the same single NULL marker.

Edge Cases

• Aggregate functions such as SUM, AVG, and COUNT(column) skip NULLs rather than treating them as zero — this can silently produce misleading averages if not accounted for.
• A UNIQUE constraint typically allows multiple NULLs in the same column, because NULL is not considered equal to another NULL for uniqueness-checking purposes — another practical departure from pure set semantics.
• A primary-key attribute may never be NULL (the entity integrity rule) — precisely because keys must reliably distinguish every tuple, which NULL's ambiguous equality would undermine.

Key Takeaways / Q&A

Q: Does `WHERE gpa = NULL` ever return true? A: No — NULL comparisons always yield UNKNOWN; you must use IS NULL instead.

Q: Why can't a primary key column contain NULL? A: Because a key's entire purpose is to uniquely identify each tuple, and NULL's ambiguous equality semantics would make uniqueness/identity undecidable.