1.3 Executing SQL statements

1.3.1 The null value

SQL offers the null value to represent missing information, or information that is not applicable in a particular context. All columns of a table can accept the null value -- unless prohibited by integrity constraints -- regardless of the domain of the column. Hence, the null value is included in all domains of a relational database and can be viewed as an out-of-band value.

Relational database theory adopted a three-valued logic system -- "true", "false", and "null" (or "unknown") -- in order to process expressions involving the null value. This system has interesting (and sometimes frustrating) consequences when evaluating arithmetic and comparison expressions. If an operand of an arithmetic expression is the null value, the expression evaluates to the null value. If a comparand of a comparison expression is the null value, the expression may evaluate to the null/unknown truth-value.

For example:

a + b, where a contains the null value or b contains the null value, evaluates to the null value
a + b, where a contains the null value and b contains the null value, evaluates to the null value
a = b, where a contains the null value or b contains the null value, evaluates to unknown
a = b, where a contains the null value and b contains the null value, evaluates to unknown
a | b, where a is true and b contains the null value, evaluates to true
a & b, where a is false and b contains the null value, evaluates to false

The SQL SELECT statements return records for which the WHERE clause (or WHERE predicate) evaluates to true (not to false and not to the null value). In order to test for the presence or absence of the null value, SQL provides a special predicate of the form

column-name is [not] null

The null value is effectively a universal value that is difficult to use efficiently in Dylan. To identify when null values are returned from or need to be sent to a DBMS server, the SQL-ODBC library supports indicator objects. Indicator objects indicate when a column of a record retrieved from a database contains the null value, or when a client application wishes to set a column to the null value.