Neoview SQL Reference Manual (R2.5)

WHERE EXISTS
(SELECT partnum FROM invent.partloc PL
WHERE PS.partnum = PL.partnum AND qty_on_hand < 20);
PARTNUM SUPPNUM
------- -------
212 1
212 3
2001 1
2003 2
...
--- 18 row(s) selected.
IN Predicate
• “Considerations for IN”
• “Examples of IN”
The IN predicate determines if a sequence of values is equal to any of the sequences of values
in a list of sequences. The NOT operator reverses its truth value. For example, if IN is true, NOT
IN is false.
row-value-constructor
[NOT] IN {table-subquery | in-value-list}
row-value-constructor is:
(expression [,expression]...)
| row-subquery
in-value-list is:
(expression [,expression]...)
row-value-constructor
specifies the first operand of the IN predicate. The first operand can be either of:
(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 255).
row-subquery
is a subquery that returns a single row (consisting of a sequence of values). See “Subquery”
(page 307).
table-subquery
is a subquery that returns a table (consisting of rows of columns). The table specifies rows
of values to be compared with the row of values specified by the row-value-constructor.
The number of values of the row-value-constructor must be equal to the number of
columns in the result table of the table-subquery, and the data types of the values must
be comparable.
in-value-list
is a sequence of SQL value expressions, separated by commas and enclosed in parentheses.
expression cannot include an aggregate function defined on a column. expression can
be a scalar subquery (a subquery that returns a single row consisting of a single column). In
this case, the result of the row-value-constructor is a single value. The data types of
the values must be comparable. The number of expressions in the in-value-list can have
at least 5000 expressions.
292 SQL Language Elements