Premium

NULL

Basic SQL
≈ 2 min read

NULL means there is no known value

NULL in SQL does not mean the number 0, an empty string, or the word “null”. It indicates that there is no known value in that place. For example, a user's phone number may not have been provided yet. 

INSERT INTO users (id, name, email)
VALUES (1, 'Anna', NULL);

In this row, the email value is unknown or absent. This is fundamentally different from '': an empty string is a specific string value.

NULL is not an ordinary value

Ordinary comparisons assume that both values are known. With NULL, that assumption is not true, so expressions involving it behave differently from comparisons between numbers or strings. SQL uses dedicated constructs to test for missing values. 

SELECT id, name
FROM users
WHERE email IS NULL;

IS NULL specifically tests for the absence of a value. We will study row filtering and related conditions in detail in the next section.

Do not replace NULL with an empty value just for convenience. If “the value is unknown” and “the value is known and empty” mean different things in the domain, the database should be able to distinguish those states.
Back Next