NULL value

User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements
NULL value

Function

To specify a value that is unknown or not applicable.

Syntax

NULL

Usage

Anywhere.

Permissions

Must be connected to the database.

Side effects

None.

Description

The NULL value is a special value which is different from any valid value for any data type. However, the NULL value is a legal value in any data type. The NULL value is used to represent missing or inapplicable information. Note that these are two separate and distinct cases where NULL is used:

Situation Description

missing The field does have a value, but that value is unknown.

inapplicable The field does not apply for this particular row.

Situation	Description
missing	The field does have a value, but that value is unknown.
inapplicable	The field does not apply for this particular row.

SQL allows columns to be created with the NOT NULL restriction. This means that those particular columns cannot contain the NULL value.

The NULL value introduces the concept of three valued logic to SQL. The NULL value compared using any comparison operator with any value including the NULL value is "UNKNOWN." The only search condition that returns "TRUE" is the IS NULL predicate. In SQL, rows are selected only if the search condition in the WHERE clause evaluates to TRUE; rows that evaluate to UNKNOWN or FALSE are not selected.

The IS [ NOT ] truth-value clause, where truth-valueis one of TRUE, FALSE or UNKNOWN can also be used to select rows where the NULL value is involved. See "Search conditions" for a description of this clause.

In the following examples, the column Salary contains the NULL value.

Condition Truth value Selected?

Salary = NULL UNKNOWN NO

Salary <> NULL UNKNOWN NO

NOT (Salary = NULL) UNKNOWN NO

NOT (Salary <> NULL) UNKNOWN NO

Salary = 1000 UNKNOWN NO

Salary IS NULL TRUE YES

Salary IS NOT NULL FALSE NO

Salary = 1000 IS UNKNOWN TRUE YES

Condition	Truth value	Selected?
Salary = NULL	UNKNOWN	NO
Salary <> NULL	UNKNOWN	NO
NOT (Salary = NULL)	UNKNOWN	NO
NOT (Salary <> NULL)	UNKNOWN	NO
Salary = 1000	UNKNOWN	NO
Salary IS NULL	TRUE	YES
Salary IS NOT NULL	FALSE	NO
Salary = 1000 IS UNKNOWN	TRUE	YES

The same rules apply when comparing columns from two different tables. Therefore, joining two tables together will not select rows where any of the columns compared contain the NULL value.

The NULL value also has an interesting property when used in numeric expressions. The result of any numeric expression involving the NULL value is the NULL value. This means that if the NULL value is added to a number, the result is the NULL value---not a number. If you want the NULL value to be treated as 0, you must use the ISNULL(expression, 0 ) function (see "Watcom-SQL Functions").

Many common errors in formulating SQL queries are caused by the behavior of NULL. You will have to be careful to avoid these problem areas. See "Search conditions" for a description of the effect of three-valued logic when combining search conditions.

Example

The following INSERT statement inserts a NULL into the date_returned column of the Borrowed_book table.

     INSERT
     INTO Borrowed_book
     ( date_borrowed, date_returned, book )
     VALUES ( CURRENT DATE, NULL, '1234' )