SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-40
Multivalued Comparison Predicate
If the operand of the IS NULL predicate evaluates to null, then the IS NULL predicate
evaluates to true; otherwise, IS NULL evaluates to false. For example, this predicate
finds all rows with null values in the FIRST_NAME column:
FIRST_NAME IS NULL
Similarly, if the operand of the IS NULL predicate evaluates to a value other than null,
then the IS NOT NULL predicate evaluates to true; otherwise, IS NOT NULL evaluates
to false. This predicate evaluates to true if the value in :jobcode is not null:
:jobcode IS NOT NULL
Multivalued Comparison Predicate
A multivalued comparison predicate specifies more than one value in the same
predicate.
These examples show two formulations of the same query. The first example uses the
AND and OR operators to retrieve the desired result. The second example shows how
you can simplify the query using a multivalued predicate.
This query, written with the AND and OR operators, lists all employees whose names
follow JAMES SMITH:
SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE LAST_NAME > "Smith"
OR ( LAST_NAME = "Smith"
AND FIRST_NAME > "James" ) ;
Table 1-4. Evaluation of Expressions That Contain Null Values
Expression Type Condition Result
Boolean (AND, OR, NOT) Either value is null or
both values are null.
Null
Arithmetic Either value is null or
both values are null.
Null
Aggregate (except COUNT) Expression is evaluated after
eliminating nulls.
Null if set is empty
COUNT DISTINCT Expression is evaluated after
eliminating nulls.
Zero if set is empty
COUNT Expression is evaluated without
eliminating nulls.
Zero if set is empty
> < = >=<= <> LIKE Either value is null or
both values are null.
Null
IN predicate Expression is null. Null
Subquery No values are returned. Null