*The Truth-Value UNKNOWN*

On June 28, 2014, In The Database Language SQL by Admin

Views (1542)

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:

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.

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:

SELECT *

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:

SELECT *

FROM Movie

WHERE studioName = ’Disney’ AND year = 1990

ORDER BY length, title;

### Tags

- logical operators
- sql
- tuple
- null values
- Queries in PSM
- Using Shared Variables
- System Aspects of SQL
- Instead-Of Triggers
- Constraints on Attributes and Tuples
- Deferring the Checking of Constraints
- Maintaining Referential Integrity
- Declaring Foreign-Key Constraints
- Modifying Views
- View Definitions
- Default Values / Indexes
- Simple Table Declarations
- Defining a Relation Schema in SQL
- Natural Joins / Outerjoins
- Subqueries in FROM Clauses
- Correlated Subqueries
- Conditions Involving Tuples
- Subqueries
- Tuple Variables
- Disambiguating Attributes
- Queries Involving More Than One Relation
- Null Values and Comparisons Involving NULL
- Dates and Times
- Comparison of Strings
- Projection in SQL
- The Database Language SQL
- Referential Integrity Constraints
- Constraints on Relations
- Natural Joins / Theta-Joins
- Object-Oriented Versus Object-Relational
- References
- Representing Set-Valued Attributes
- From ODL Designs to Relational Designs
- Other Data Models
- Instances of an E/R Diagram
- Database Programming