The Truth-Value UNKNOWN

The Truth-Value UNKNOWN

In "Selection in SQL" we assumed that the result of a comparison was either TRUE or FALSE, and these truth-values were combined in the obvious way using the logical operators AND, OR and NOT. We have just seen that when NULL values occur, comparisons can yield a third truth-value: UNKNOWN. We must now learn how the logical operators behave on combinations of all three truth-values.

The rule is easy to remember if we think of TRUE as 1 (i.e., fully true), FALSE as 0 (i.e., not at all true), and UNKNOWN as 1/2 (i.e., somewhere between true and false). Then:

Pitfalls Regarding Nulls

1.  The AND of two truth-values is the minimum of those values. That is, x AND y is FALSE if either x or y is FALSE; it is UNKNOWN if neither is FALSE but at least one is UNKNOWN, and it is TRUE only when both x and y are TRUE.

2.  The OR of two truth-values is the maximum of those values. That is, x OR y is TRUE if either x or y is TRUE; it is UNKNOWN if neither is TRUE but at least one is UNKNOWN, and it is FALSE only when both are FALSE.

3.  The negation of truth-value v is 1-v. That is, NOT x has the value TRUE when x is FALSE, the value FALSE when x is TRUE, and the value UNKNOWN when x has value UNKNOWN.

In Figure 1 is a summary of the result of applying the three logical operators to the nine different combinations of truth-values for operands x and y. The value of the last operator, NOT, depends only on x.

Truth table for three-valued logic

SQL conditions, as appear in WHERE clauses of select-from-where statements, apply to each tuple in some relation, and for each tuple, one of the three truth values, TRUE, FALSE, or UNKNOWN is produced. On the other hand, only the tuples for which the condition has the value TRUE become part of the answer; tuples with either UNKNOWN or FALSE as value are excluded from the answer. That situation leads to another surprising behavior similar to that discussed in the box on "Pitfalls Regarding Nulls", as the next example shows.

Example 1: Suppose we ask about our running-example relation

Movie(title, year, length, inColor, studioName, producerC#)

the following query:

FROM Movie
WHERE length <= 120 OR length > 120;

Intuitively, we would expect to get a copy of the Movie relation, since each movie has a length that is either 120 or less or that is greater than 120.

However, assume there are Movie tuples with NULL in the length component. Then both comparisons length <= 120 and length > 120 evaluate to UNKNOWN. The OR of two UNKNOWNs is UNKNOWN, by Figure 1. Thus, for any tuple with a NULL in the length component, the WHERE clause evaluates to UNKNOWN. Such a tuple is not returned as part of the answer to the query. As a result, the true meaning of the query is "find all the Movie tuples with non-NULL lengths".

Ordering the Output

We may ask that the tuples produced by a query be presented in sorted order. The order may be based on the value of any attribute, with ties broken by the value of a second attribute, remaining ties broken by a third, and so on, as in the r operation of "Extending the Projection Operator". To get output in sorted order, we add to the select-from-where statement a clause:

ORDER BY <list of attributes>

The order is by default ascending, but we can get the output highest-first by appending the keyword DESC (for "descending") to an attribute. Similarly, we can specify ascending order with the keyword ASC, but that word is unnecessary.

Example 1 : The following is a rewrite of our original query of "The Database Language SQL" Example 1, asking for the Disney movies of 1990 from the relation

Movie(title, year, length, inColor, studioName, producer#)

To get the movies listed by length, shortest first, and among movies of equal length, alphabetically, we can say:

FROM Movie
WHERE studioName = ’Disney’ AND year = 1990
ORDER BY length, title;