NULL Values

NULL Values

A field with no value at all is called NULL. NULL is NOT the same as zero or an empty string - it means "no data was stored".

NULL vs zero vs empty

• 0 is a real number.
• '' is an empty text.
• NULL means "no value exists".

How to test for NULL

You cannot use = with NULL. You must use IS NULL or IS NOT NULL:

SELECT CustomerName, ContactName
FROM Customers
WHERE ContactName IS NULL;

SELECT CustomerName, ContactName
FROM Customers
WHERE ContactName IS NOT NULL;

Why = NULL fails

NULL = NULL is not true - NULL is unknown, so any comparison to it is unknown. That is why IS NULL exists.

NULL in arithmetic

Any calculation with NULL becomes NULL:

SELECT Price * 2 FROM Products;   -- if Price is NULL, result is NULL

TL;DR

• NULL means "no value".
• Test with IS NULL / IS NOT NULL, never = NULL.
• Zero and empty string are real values; NULL is not.