SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-39
IS NULL Predicate
SOME and ANY are synonyms. In this example, a SOME query is formulated to select
information about employees who are also managers. Note that this example is
logically equivalent (that is, it retrieves the same data) to the EXISTS example
previously discussed.
SELECT EMP_ID, LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE EMP_ID = SOME (SELECT MGR_ID
FROM EMPLOYEE) ;
The query returns this result:
EMP_ID LAST_NAME FIRST_NAME
------ --------- ----------
2705 Simpson Travis
2906 Nakagawa Etsuro
3598 Nakamura Eichiro
9069 Smith John
--- 4 row(s) selected.
If the quantified predicate compares two character strings, these guidelines apply to
the use of character sets:
•
The same character set must be associated with each of the two character
expressions. Any character data type is compatible with other character data types
as long as both have the same associated character set.
•
If neither character expression is a column with an associated collation, a binary
comparison is used for all comparisons.
•
If the character expressions have two different lengths, the shorter string is filled on
the right with spaces to match the length of the longer string. Spaces are used
whether or not a single-byte or double-byte character set is associated with the
expression.
IS NULL Predicate
You can use the IS NULL and IS NOT NULL predicates to determine whether a column
contains an unknown (null) value.
The existence of null values produces logic with three possible values: true, false, and
unknown.
Table 1-4 on page 1-40 summarizes expression evaluation with null values. For
complete syntax and details on each type of expression, see the SQL/MP Reference
Manual.