In Defense of Nulls (3)

In Defense of Nulls

3-Valued Logic (3VL)

Much of the power of nulls to simplify database access comes from the use of 3-valued logic. In relational 3-valued logic there are 3 logic values - TRUE, FALSE and UNKNOWN. The UNKNOWN logic value is used when any operand is null in a operation producing a logical result like comparison. The logical operators (AND, OR, NOT) are appropriately extended for UNKNOWN logic values.

A great deal of the controversy over nulls has centered on 3-valued logic. It has been condemned as 'counterintuitive'. But, for many database users, 2-valued logic is counterintuitive in its own right. See Tom Johnston's discussion of 'AND is not and', 'OR is not or' in Part I of 'MVL:Case Open'.

In the real world, things are not always black or white, they are sometimes gray. When attorneys ask a judge to instruct a witness to answer their question with a simple yes or no, the witness may still respond - I don't know, I don't recall or, I refuse to answer on the grounds that it may tend to incriminate me (taking the Fifth). It also occurs in database access. When a database is queried as to whether a person was born before 1950 or in or after 1950, the answer may be UNKNOWN if the birth year stored in the database is missing (null).

In 2-valued logic, the basic logical operations (AND, OR, NOT) can be described as follows,

AND True if both operands are True,
False if either operand (or both) is False.

OR True if either operand (or both) is True,
False if both operands are False.

NOT True if operand is False,
False if operand is True.

For 3-valued logic, simple but powerful additions are made,

AND True if both operands are True,
False if either operand (or both) is False,
Unknown otherwise (if one operand is Unknown and the other is Unknown or True).

OR True if either operand (or both) is True,
False if both operands are False.
Unknown otherwise (if one operand is Unknown and the other is Unknown or False).

NOT True if operand is False,
False if operand is True,
Unknown if operand is Unknown.

This last addition (to the NOT operator) has stirred the most controversy. This is because in English 'not unknown' is considered to be 'known'. In the 3-valued logic system, 'known' is either True or False. Since the value of the operand is Unknown in this case, it cannot be determined whether to use True or False, so the value of NOT Unknown is Unknown. In other words, the NOT operator does not change the Unknown logic value.

This is best illustrated with an example. If a database column is queried with the comparison - BirthYear < 1950 and the value of the BirthYear column is missing (null), the logic value of the comparison is Unknown. This is because the actual BirthYear value could be either < 1950 or >= 1950, but the value is currently missing.

So there are 3 possible answers to the query - BirthYear < 1950 :

True, BirthYear < 1950
False, BirthYear >= 1950
Unknown, BirthYear is missing

When the expression is changed to NOT BirthYear < 1950 (algebraically equivalent to BirthYear >= 1950) we get :

True, BirthYear >= 1950
False, BirthYear < 1950
Unknown, BirthYear is missing

When BirthYear is missing, the result of BirthYear < 1950 and the result of NOT BirthYear < 1950 are the same - Unknown. While these concepts are sometimes difficult to understand, they add greatly to the expressive power of relational logic.

In the next section, SQL Problems with 3-Valued Logic is discussed.

Return to Contents Page: In Defense of Nulls Return to Issues Page

AND	True if both operands are True, False if either operand (or both) is False.
OR	True if either operand (or both) is True, False if both operands are False.
NOT	True if operand is False, False if operand is True.