Neoview SQL Reference Manual (R2.2)

------- ------------------
186 186 MegaByte Disk
--- 1 row(s) selected.
• Find those items (and their suppliers) in PARTS that have a supplier in the PARTSUPP table:
SELECT P.partnum, P.partdesc, S.suppnum, S.suppname
FROM sales.parts P,
invent.supplier S
WHERE P.partnum, S.suppnum IN
(SELECT partnum, suppnum
FROM invent.partsupp);
• Find those employees in EMPLOYEE whose last name and job code match the list of last
names and job codes:
SELECT empnum, last_name, first_name
FROM persnl.employee
WHERE (last_name, jobcode) IN
(VALUES ('CLARK', 500), ('GREEN', 200));
NULL Predicate
The NULL predicate determines whether all the expressions in a sequence are null. See “Null”
(page 230).
row-value-constructor IS [NOT] NULL
row-value-constructor is:
(expression [,expression]...)
| row-subquery
row-value-constructor
specifies the operand of the NULL predicate. The operand can be either of these:
(expression [,expression ]...)
is a sequence of SQL value expressions, separated by commas and enclosed in parentheses.
expression cannot include an aggregate function unless expression is in a HAVING
clause. expression can be a scalar subquery (a subquery that returns a single row
consisting of a single column). See “Expressions” (page 208).
row-subquery
is a subquery that returns a single row (consisting of a sequence of values). See “Subquery”
(page 250).
If all of the expressions in the row-value-constructor are null, the IS NULL predicate is
true. Otherwise, it is false. If none of the expressions in the row-value-constructor are null,
the IS NOT NULL predicate is true. Otherwise, it is false.
Considerations for NULL
Summary of NULL Results
Let rvc be the value of the row-value-constructor. This table summarizes the results of
NULL predicates. The degree of a rvc is the number of values in the rvc.
NOT rvc IS NOT
NULL
NOT rvcIS NULLrvc IS NOT NULLrvc IS NULLExpressions
TRUEFALSEFALSETRUEdegree 1: null
FALSETRUETRUEFALSEdegree 1: not null
TRUEFALSEFALSETRUEdegree>1: all null
TRUETRUEFALSEFALSEdegree>1: some null
FALSETRUETRUEFALSEdegree>1: none null
Predicates 241