Null Values

The value NULL indicates the absence of a value. It is a placeholder for a value that is missing. Use a NULL when the actual value is not known or when a value would not be meaningful. Do not use NULL to represent a numeric value of zero, because they are not equivalent. Any parameter in an expression can contain NULL regardless of its data type. In addition, any column in a table can contain NULL, regardless of its data type, unless you specify NOT NULL or PRIMARY KEY integrity constraints for the column when you create the table.

The following properties of NULL affect operations on rows, parameters, or local variables:

  • By default, NULL is sorted as the highest value in a sequence of values. However, you can modify the sort order value for NULL with NULLS FIRST or NULLS LAST in the ORDER BY clause.

  • Two NULL values are not equal to each other except in a GROUP BY or SELECT DISTINCT operation.

  • An arithmetic expression containing a NULL evaluates to NULL. In fact, all operators (except concatenation) return NULL when given a NULL operand. For example, (5-col), where col is NULL, evaluates to NULL.

  • To test for NULL, use the comparison conditions IS NULL or IS NOT NULL. Because NULL represents a lack of data, a NULL cannot be equal or unequal to any value or to another NULL. Thus, the statement select * from employees where mgr_id = NULL evaluates to 0, since you cannot use this comparison to NULL. However, the statement select * from employees where mgr_id is NULL provides the CEO of the company, since that is the only employee without a manager. For details, see "IS NULL Predicate".

  • You can use the NULL value itself directly as an operand of an operator or predicate. For example, the (1 = NULL) comparison is supported. This is the same as if you cast NULL to the appropriate data type, as follows: (1 = CAST(NULL AS INT)). Both methods are supported and return the same results.

Because of these properties, TimesTen ignores columns, rows, or parameters containing NULL when:

  • Joining tables if the join is on a column containing NULL.

  • Executing aggregate functions.

In several SQL predicates, you can explicitly test for NULL. APIs supported by TimesTen offer ways to handle null values. For example, in an ODBC application, use the functions SQLBindCol, SQLBindParameter, SQLGetData, and SQLParamData to handle input and output of NULL values.