NULL and three-valued logic

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:

1 2 3 4

NULL = 0 NULL <> 0 NULL > 0 NULL = NULL

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:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     customer_id,     first_name,     last_name,     phone FROM     sales.customers WHERE     phone = NULL ORDER BY     first_name,     last_name;

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.

1	phone = NULL;

Therefore, you get an empty result set.

To test whether a value is NULL or not, you always use the IS NULL operator.

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     customer_id,     first_name,     last_name,     phone FROM     sales.customers WHERE     phone IS NULL ORDER BY     first_name,     last_name;

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:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     customer_id,     first_name,     last_name,     phone FROM     sales.customers WHERE     phone IS NOT NULL ORDER BY     first_name,     last_name;

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