Course contentsShow
← Computer Science
Lesson 1558 of 2,87236. Database FundamentalsFree lesson

NULL Values and Their Semantics

Handling missing or unknown information with NULL values and understanding their behavior in comparisons and operations.

NULL Values and Their Semantics

You'll learn how databases represent missing or unknown information using NULL, and why NULL behaves differently from regular values.

What is NULL?

In relational databases, NULL represents the absence of a value—not zero, not an empty string, but truly unknown or missing data. Think of it like an empty box: you don't know what's supposed to be inside, or if anything belongs there at all.

For example, if a customer table has an optional "middle name" column, a person without a middle name would have NULL in that field—not because their middle name is blank, but because it doesn't exist.

NULL in Comparisons

Here's where NULL gets tricky: NULL is not equal to anything, not even itself.

  • NULL = NULL returns NULL (unknown), not TRUE
  • NULL != 5 also returns NULL, not TRUE
  • Any arithmetic operation with NULL yields NULL: 5 + NULL = NULL

This is called three-valued logic: comparisons can be TRUE, FALSE, or UNKNOWN (NULL).

To check for NULL values, use special operators:

  • IS NULL — checks if a value is NULL
  • IS NOT NULL — checks if a value exists

Why This Matters

NULL semantics affect queries and constraints. A WHERE age > 30 filter won't include rows where age is NULL—they're excluded because the comparison returns UNKNOWN. Foreign key constraints typically allow NULL (meaning "no relationship"), unless you explicitly disallow it.

Key Takeaway: NULL represents missing or unknown data and behaves uniquely in comparisons—always use IS NULL or IS NOT NULL to test for NULL values, never equality operators.