NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
N-6
Examples—NULL
The following chart summarizes expression evaluation for null predicates. rvs
stands for row value specification. Degree is the number of expressions in row value
specification.
Note that the expression
row-value-specification IS NOT NULL
is not equivalent to the expression
NOT ( row-value-specification IS NULL )
•
If all the expressions in the NULL predicate evaluate to null, then the IS NULL
predicate evaluates to TRUE; otherwise, IS NULL evaluates to FALSE.
Examples—NULL
•
The following predicate finds all rows with a null value in the SALARY column:
SALARY IS NULL
•
The following predicate evaluates to true if the expression (PRICE + TAX)
evaluates to null:
(PRICE + TAX) IS NULL
•
The following predicate evaluates to true if the value in :JOBCODE is not null:
:JOBCODE IS NOT NULL
•
The following predicate finds all rows where both FIRST_NAME and SALARY
have a null value:
FIRST_NAME, SALARY IS NULL
Null Values
A null value is a special symbol, independent of data type, that represents an unknown
or inapplicable value. A null value indicates that an item has no value. For sorting
purposes, SQL considers null values greater than all other values.
Expression
rvs
is
NULL
rvs is NOT
NULL
NOT rvs is
NULL
NOT rvs is
NOT NULL
degree 1:
null
TRUE FALSE FALSE TRUE
degree 1:
not null
FALSE TRUE TRUE FALSE
degree>1:
all null
TRUE FALSE FALSE TRUE
degree>1:
some null
FALSE FALSE TRUE TRUE
degree>1:
none null
FALSE TRUE TRUE FALSE