NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-81
Subqueries
Subqueries
A subquery is a special form of the SELECT statement that selects only for the purpose
of comparison. You can specify a subquery in a comparison, EXISTS, IN, or qualified
predicate of a search condition.
See SELECT Statement
on page S-18 for the complete syntax and rules for the elements
and clauses of a subquery. See Comparison Predicate on page C-53 or EXISTS
Predicate on page E-12 for examples that use subqueries.
Considerations—Subqueries
•
A SELECT statement that contains a subquery is called an outer query. The
subquery within the SELECT is called an inner query. The differences between a
SELECT statement and a subquery are as follows:
°
A subquery is always enclosed in parentheses.
°
A subquery returns a result table of one column.
°
A SELECT statement can retrieve values to place in a cursor or host variable. A
subquery searches for values to use in comparisons. The comparisons determine
whether a search condition is satisfied.
°
The INTO clause of a SELECT statement cannot be associated with a subquery
in a cursor declaration. Therefore, the subquery cannot be used to retrieve values
for host variables.
°
A SELECT statement can specify a list of elements to select. A subquery can
specify only a single column or expression. You can, however, specify an
asterisk or a correlation name followed by an asterisk if the subquery occurs in
( SELECT [ ALL | DISTINCT ] { expression }
{ * }
{ correlation-name.* }
{ table-name.* }
{ view-name.* }
FROM table-ref [, table-ref] ...
[ WHERE search-condition]
[ HAVING search-condition]
[ [ FOR ] { BROWSE | STABLE | REPEATABLE } ACCESS ]
[ IN { SHARE | EXCLUSIVE } MODE ]
[ GROUP BY column-name [, column-name] ] ...
[ UNION [ ALL ] select-statement] )