Nulls and Three-Valued Logic (One More Time)

Most of the operations of relational algebra involve the use of logical operators, operators that usually return a Boolean resultthat is, True or False. I say "usually" because, with the addition of Nulls to the relational model, things get a little more complicated.

Nulls add a third value to the set of Boolean values; you must then work with True, False, and Null. Not surprisingly, these operators become known as three-valued logic. The three-valued truth tables for the standard logical operators are shown in Figure 5-1.

As you can see, Null op anything, where op is a logical operator, results in Null. This is generally also true of the logical comparison operators, as shown in Figure 5-2.

There are two exceptions to this. Microsoft Jet returns True instead of Null as the result of "True Xor Null" (which kind of makes sense), and SQL Server, for reasons that I'm sure make sense to its designers, adds an "extension" to normal logical operations. If the option ANSI_NULLS is turned off, Null = Null evaluates to True, and Null = <value>, where <value> is anything except Null (including the Boolean values True and False), evaluates to False. Frankly, I can't imagine why anyone would want to break the rules of relational algebra in this wayworking with Nulls is complicated enough without adding uncertainty about how operators will behave.

SQL provides two unary operatorsIS NULL and IS NOT NULLto specifically handle Null values. They work exactly as one might expect. The truth tables for IS NULL and IS NOT NULL are shown in Figure 5-3. Again, <value> indicates anything except Null.