SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-106
Subquery
When you use rowset-search-condition in a subquery, all the individual
search-conditions in the rowset are applied successively. The result table is the
union of all the rows selected by these successive applications. Using rowsets in a
subquery implies that the entire rowset in the subquery is evaluated and the result
table passed on the outer query. If the outer query has rowset-search-
conditions, for each element in the outer query rowset-search-condition,
NonStop SQL/MX will use the entire result table from the subquery, obtained by
evaluating all the search conditions in the subquery rowset-search-condition.
SELECT Form of a Subquery
A subquery is typically specified as a special form of a SELECT statement enclosed in
parentheses that queries (or selects) to provide values in a search condition or to
specify a derived table as a table reference.
The form of a subquery specified as a SELECT statement is:
Notice that an ORDER BY clause is not allowed in a subquery.
Using Subqueries to Provide Comparison Values
When a subquery is used to provide comparison values, the SELECT statement that
contains the subquery is called an outer query. The subquery within the SELECT is
called an inner query. In this case, the differences between the SELECT statement and
the SELECT form of a subquery are:
•
A subquery is always enclosed in parentheses.
•
A subquery cannot contain an ORDER BY clause.
•
If a subquery is not part of an EXISTS, IN, or quantified comparison predicate, and
the subquery evaluates to more than one row, a run-time error occurs.
Nested Subqueries When Providing Comparison Values
An outer query (a main SELECT statement) can have up to 15 levels of nested
subqueries. Subqueries within the same WHERE or HAVING clause are at the same
level. For example, this query has one level of nesting:
SELECT * FROM TABLE1
WHERE A = (SELECT P FROM TABLE2 WHERE Q = 1)
AND B = (SELECT X FROM TABLE3 WHERE Y = 2)
( SELECT [ALL | DISTINCT] select-list
FROM table-ref [,table-ref]...
[WHERE search-condition | rowset-search-condition]
[GROUP BY colname [,colname]...]
[HAVING search-condition | rowset-search-condition]
[[FOR] access-option ACCESS]
[IN {SHARE | EXCLUSIVE} MODE]
[UNION [ALL] select-stmt] )