The NULL value

Imagine you've created a table to store data about Nobel prizes. You defined the following columns: year, field, winner's name, and year of winner's birth to evaluate the age of winners. However, for some winners, the year of birth is undefined, for instance, for the European Union citizens that received the Nobel Peace Prize in 2012. You may put some special value like $0$ in these cells, but it may potentially lead to mistakes. For such cases, SQL has a special value: NULL. In this topic we'll discuss how to use it.

NULL value

NULL is used in SQL to indicate that some data value is unknown or undefined. It should not be confused with a value of $0$ or ''. Arithmetic or string expressions with NULL among the operands are evaluated as NULL, for example, $2+2*NULL$ equals NULL.

A NULL value can be stored in a column of any type. However, a software engineer may use a NOT NULL constraint in create table statement to specify that a column should not store NULL values. For example, according to this code, only the column "winner_birth_year" may contain NULLs:

CREATE TABLE winners ( 
    year INTEGER NOT NULL,
    field VARCHAR(20) NOT NULL, 
    winner_name VARCHAR(100) NOT NULL, 
    winner_birth_year INTEGER);

Comparisons with NULL

NULL value basically means "value is not present". Because of this, comparisons with NULL can never result in either TRUE or FALSE, but always in a third logical result, UNKNOWN. It is, actually, the NULL value of boolean type and some data management systems do not distinguish a special UNKNOWN value. For example, the result of each of the following comparisons is UNKNOWN:

• NULL = 1

• NULL <> 1

• NULL > 1

• NULL = '1'

• NULL = NULL

How can we check whether an expression or a value is null or not? SQL supports special predicates for that: IS NULL and IS NOT NULL. For example, queries below both return TRUE as a result:

SELECT 0+NULL IS NULL;
SELECT '' IS NOT NULL;

The following queries are evaluated as FALSE:

SELECT NULL IS NOT NULL;
SELECT 1-1 IS NULL;

TRUE, FALSE, and UNKNOWN

In logic expressions with UNKNOWN operands, the result is UNKNOWN if it depends on an operand that is UNKNOWN. Thus, in contrast to comparisons, the result of a logic expression can be something other than UNKNOWN even though one operand is UNKNOWN. Let's consider the following examples:

• (NULL = 1) AND TRUE evaluates to UNKNOWN (the result would be TRUE only if both operands were TRUE),

• (NULL = 1) OR TRUE equals to TRUE (the result is TRUE because at least one operand is TRUE).

Conclusion

Now you know how to work with data when there is actually no data and use NULL values where they are needed. Let's repeat the key points of the NULL value in SQL:

• NULL is used in SQL to indicate that some data value is unknown or undefined.

• Use NOT NULL constraint to specify that a column should not store NULL values.

• Check whether an expression or a value is null or not with IS NULL and IS NOT NULL.

• Logic is also possible with the NULL values: TRUE, FALSE, and UNKNOWN could be the result of an expression.