Chapter 6 SQL Language Elements
261
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" on page 303).
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" on page 239 for a description of the effect of three-
valued logic when combining search conditions.
♦
SQL/92 Entry-level feature.
♦
Sybase In some contexts, Adaptive Server Enterprise treats NULL as
a value, whereas Adaptive Server Anywhere does not. For example,
rows of a column
c1
that are NULL are not included in the results of a
query with the following WHERE clause in Adaptive Server Anywhere,
as the condition has a value of UNKNOWN:
WHERE NOT( C1 = NULL )
In Adaptive Server Enterprise, the condition is evaluated as TRUE, and
these rows are returned. You should use IS NULL rather than a
comparison operator for compatibility.
Unique indexes in Adaptive Server Anywhere can hold rows that hold
NULL and are otherwise identical. Adaptive Server Enterprise does not
permit such entries in unique indexes.
♦ The following INSERT statement inserts a NULL into the
date_returned
column of the
Borrowed_book
table.
Standards and
compatibility
Example
To Next Page
Loading...