Section 4 — NULL and Empty Values

What does NULL mean in a database?

A value is missing, unknown, or not applicable.

Is NULL the same as 0?

No. Zero is a known numeric value; NULL means no known value.

Is NULL the same as an empty string?

No. An empty string has zero characters; NULL has no value.

How do you find NULL values?

Use IS NULL. Example: WHERE FirstPurchase IS NULL.

How do you find non-NULL values?

Use IS NOT NULL. Example: WHERE ContactPref IS NOT NULL.

How do you find an empty text value?

Use = ''. Example: WHERE Name = ''.

How do you find a nonempty text value?

Use != ''. Example: WHERE Name != ''.

Why can’t you write WHERE Field = NULL?

NULL is not compared using =; use IS NULL instead.

Example: customers with no first-purchase date

SELECT Name FROM Customer WHERE FirstPurchase IS NULL;

Example: customers with a contact preference

SELECT Name, ContactPref FROM Customer WHERE ContactPref IS NOT NULL;

What does an empty string contain?

Zero characters.

Why are NULL values important in calculations?

A calculation involving NULL often produces NULL because the value is unknown.