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 result—that 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.

Figure 5-1. The three-valued And, Or, and XOr truth tables.

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.

Figure 5-2. The three-valued Equal and Not Equal truth tables.

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, evaluates to False. (This is undoubtedly related to the issue of allowing only a single Null value in UNIQUE indices.)

SQL provides two unary operators—IS NULL and IS NOT NULL—to 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.

Figure 5-3. The IS NULL and IS NOT NULL truth tables.