NULL in Relational Databases

Lesson 1.6 · Reading time: ~6 min · Practice tasks ↓

NULL is the special marker relational databases use when a value is missing, unknown, or doesn't apply. In this lesson you'll learn what NULL means, what it is not, why databases need it, which columns may contain it, and why NULL makes the database answer "unknown" instead of "yes" or "no".

In the previous lessons we looked at tables, rows, columns and data types. Every cell of a table holds a value of its column's type - except when there is no value to store at all.

What Does NULL Mean?

NULL is not a value but a marker: it says that the cell has no value. The reason can differ - the value is unknown, it doesn't apply to this row, or it simply hasn't appeared yet.

NULL in a Table

The rental table of the Sakila database stores film rentals. A rental gets its return_date only when the film comes back, so until then the cell is NULL:

Rental 14098 hasn't been returned: its return date doesn't exist yet. It isn't "zero" or "an empty date" - there is simply nothing there.

What NULL Is Not

• NULL is not 0. Zero is a real number: a price of 0 means "free", a NULL price means "the price is unknown".
• NULL is not an empty string. '' is text with no characters; it's a known value. NULL means there is no text at all.
• NULL is not the word 'NULL'. The text 'NULL' is an ordinary string of four letters.
• NULL is not false. In database logic NULL means "unknown", not "no".

Why Do Databases Use NULL?

Without NULL, every missing value would need a made-up placeholder like 0, '' or 1900-01-01, and such placeholders get mixed up with real data. NULL covers the typical cases honestly:

• Unknown: a customer didn't give a phone number.
• Not applicable: a private person has no company tax ID.
• Not yet known: a rental has no return date until the film comes back.

Which Columns Can Contain NULL?

Whether a column accepts NULL is decided when the table is designed. A column declared NOT NULL must always have a value: in rental, the rental_date is required, while return_date may stay empty. A primary key can never be NULL: a row must always be identifiable.

A good rule of thumb: allow NULL only where a missing value really makes sense, and mark everything else NOT NULL.

NULL in Calculations

If an expression uses NULL, the result is usually NULL too, because an unknown value makes the whole result unknown:

• 10 + NULL is NULL.
• 5 * NULL is NULL.
• The number of days between a rental date and a NULL return date is NULL - the rental isn't over yet.

Three-Valued Logic

Usually a condition is either true or false. With NULL, a third answer appears: UNKNOWN. Was rental 14098 returned before August 25? Its return date is unknown, so the honest answer is "unknown".

The same goes for comparing NULL with NULL: two unknown values are not known to be equal, so NULL = NULL is also UNKNOWN, not true. That's why databases have special checks for NULL instead of the usual comparisons - you'll use them once you start writing queries.

Key Takeaways:

• NULL marks a missing value: unknown, not applicable or not yet known.
• NULL is not 0, not an empty string, not the text 'NULL' and not false.
• Columns declared NOT NULL and primary keys can't contain NULL; other columns can.
• Calculations with NULL usually give NULL.
• Comparisons with NULL give a third logical result, UNKNOWN; even NULL = NULL is UNKNOWN.

Frequently Asked Questions

Is NULL the same as an empty string?

No. An empty string '' is a known text value with zero characters. NULL means there is no value at all.

Is NULL equal to NULL?

Not in the usual sense. Each NULL stands for an unknown value, and two unknown values can't be shown to be equal, so comparing NULL with NULL gives UNKNOWN.

Can any column contain NULL?

A column of any type - number, text or date - can hold NULL unless the table forbids it with NOT NULL. Primary key columns never accept NULL.

Interview Questions

How would you explain NULL in a database interview?

NULL is a marker for a missing value: unknown, not applicable or not yet known. It isn't zero, an empty string or false, and it follows its own rules: calculations with NULL return NULL, and comparisons with it return UNKNOWN.

What is three-valued logic?

In SQL a condition can be TRUE, FALSE or UNKNOWN. UNKNOWN appears whenever a comparison involves NULL, because the database can't tell whether an unknown value satisfies the condition.

Why can't a primary key be NULL?

A primary key identifies a row. An unknown identifier can't do that: two rows with NULL keys couldn't be told apart, so databases reject NULL in primary key columns.