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: TRUEFALSE, 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.
SQL Server IS NULL example
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:
SQL Server IS NOT NULL
In this tutorial, you have learned about NULL, three-valued logic, and how to test whether a value is NULL or not.

Post a Comment

0 Comments