SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-83
Considerations—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:

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
an EXISTS predicate or if the FROM clause refers to a single table (or view)
consisting of a single column.

A subquery cannot contain an ORDER BY clause.

If a subquery is not part of an EXISTS, IN, or quantified predicate, and the
subquery evaluates to more than one row, a run-time error occurs.

If a subquery contains references to an outer query, the subquery might be
evaluated repeatedly. This type of subquery is called a correlated
subquery, discussed later in this subsection.

An outer query (a main SELECT statement) can have up to 15 levels of nested
subqueries.
Subqueries within the same ON, 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)
A subquery within the WHERE clause of another subquery defines a new level, so
this query has two levels of nesting:
SELECT * FROM TABLE1
WHERE A = (SELECT P FROM TABLE2
WHERE Q = (SELECT X FROM TABLE3
WHERE Y = 2))

In the search condition of a subquery, you can refer to columns of any table or view
defined in an outer query. Such a reference is called an outer reference. A
subquery containing an outer reference is called a correlated subquery.