NULL
and three-valued logic
In the database world,
NULL
is used to indicate the absence of any data value. For example, at the time of recording the customer information, the email may be unknown, so it is recorded as NULL
in the database.
Normally, the result of a logical expression is
TRUE
or FALSE
. However, when NULL
is involved in the logical evaluation, the result is UNKNOWN
. This is called a three-valued logic: TRUE
, FALSE
, and UNKNOWN
.
The results of the following comparisons are
UNKNOWN
:NULL
is equal to nothing, even NULL
is not equal to NULL
because each NULL
could be different.
IS NULL
See the following
customers
table from the sample database.
The following statement finds the customers who do not have phone number recorded in the
customers
table:
The query returned an empty result set.
The
WHERE
clause returns rows that cause its predicate evaluates to TRUE
. However, the following expression evaluates to UNKNOWN
.
Therefore, you get an empty result set.
To test whether a value is
NULL
or not, you always use the IS NULL
operator.
The query returned the customers who do not have the phone information.
As you may guess, to check if a value is not
NULL
, you can use the IS NOT NULL
operator.
The following query returns customers who have phone information:

In this tutorial, you have learned about
NULL
, three-valued logic, and how to test whether a value is NULL
or not.
0 Comments