Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Language Elements

Comments Next Page

NULL value


The NULL value specifies a value that is unknown or not applicable.

Syntax

NULL

Remarks

NULL is a special value that is different from any valid value for any data type. However, the NULL value is a legal value in any data type. NULL is used to represent missing or inapplicable information. There 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.

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

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-value is one of TRUE, FALSE or UNKNOWN can 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 NULL.

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 = expression 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.

NULL also has an interesting property when used in numeric expressions. The result of any numeric expression involving the NULL value is NULL. This means that if NULL is added to a number, the result is NULL—not a number. If you want NULL to be treated as 0, you must use the ISNULL( expression, 0 ) function (see 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.

Set operators and DISTINCT clause

In set operations (UNION, INTERSECT, EXCEPT), and in the DISTINCT operation, NULL is treated differently from in search conditions. Rows that contain NULL and are otherwise identical are treated as identical for the purposes of these operations.

For example, if a column called redundant contained NULL for every row in a table T1, then the following statement would return a single row:

SELECT DISTINCT redundant FROM T1
Permissions

Must be connected to the database.

Side effects

None.

Standards and compatibility
See also
Example